User Input based data transfer from one workbook to another
I would like to have Excel -VBA code for the following scenario. I have Excel-2007 hence code should be compatible. I do not want vb.net or Visual Basic Development environment solution. I require excel resident Visual Basic Editor solution.
1. There are 2 workbooks WB1 and WB2 in closed form.
2. User to specify full path to two workbooks in two parts a) File directory path and b) File name (Graphic User Interface like forms with or without REFEDIT mode ie GUI)
3. User to specify Sheet names of two workbooks WS1 and WS2 in GUI
2. Data transfer situation could be following in either or mode ie any one of the following for 2 workbooks on user specified sheets to be specified in GUI.
a) User specified Contiguous range such as B15:W300 in WB1 on user specified sheet to be posted at user specified starting location in user specified worksheet in WB2 such as "G15"
b) User Specified Multiple Non Contiguous Ranges such as B15:j75 And N30:R100 in WB1 user specified worksheet to be copy pasted to user specified starting location for contiguous ranges on WB2 maintaing relative row offset for example A2:I62 and J17:N87
c) User specified Rows such as A30 upto last used row or user specified last row to be appended to first empty/blank row in the specified sheet also provision to be made for pasting to new additional sheet.
d) User specified Columns Contiguous or Non contiguous Multiple columns to be copy pasted at user specified starting location or to be appended to lastempty/blank column.
e) user specified copy pasting mode for values only or data with formulas mode in GUI.
I am not providing any code as my level is no match to experts level here and my piece-meal code will not serve any purpose. It may confuse more. Still if required to show my efforts I will post it on demand.
I am attaching a sample file.
Hope I have detailed out my requirements. I require full working program code.
Sample-200915.xlsx
1. There are 2 workbooks WB1 and WB2 in closed form.
2. User to specify full path to two workbooks in two parts a) File directory path and b) File name (Graphic User Interface like forms with or without REFEDIT mode ie GUI)
3. User to specify Sheet names of two workbooks WS1 and WS2 in GUI
2. Data transfer situation could be following in either or mode ie any one of the following for 2 workbooks on user specified sheets to be specified in GUI.
a) User specified Contiguous range such as B15:W300 in WB1 on user specified sheet to be posted at user specified starting location in user specified worksheet in WB2 such as "G15"
b) User Specified Multiple Non Contiguous Ranges such as B15:j75 And N30:R100 in WB1 user specified worksheet to be copy pasted to user specified starting location for contiguous ranges on WB2 maintaing relative row offset for example A2:I62 and J17:N87
c) User specified Rows such as A30 upto last used row or user specified last row to be appended to first empty/blank row in the specified sheet also provision to be made for pasting to new additional sheet.
d) User specified Columns Contiguous or Non contiguous Multiple columns to be copy pasted at user specified starting location or to be appended to lastempty/blank column.
e) user specified copy pasting mode for values only or data with formulas mode in GUI.
I am not providing any code as my level is no match to experts level here and my piece-meal code will not serve any purpose. It may confuse more. Still if required to show my efforts I will post it on demand.
I am attaching a sample file.
Hope I have detailed out my requirements. I require full working program code.
Sample-200915.xlsx
ASKER
Hi regmigrant ,
If I reduce the work scope considerably then would you like to look into. My reduced requirement is outlined below.
I would like to have Excel -VBA code for the following scenario. I have Excel-2007 hence code should be compatible. I do not want vb.net or Visual Basic Development environment solution. I require excel resident Visual Basic Editor solution.
1. There are 2 workbooks WB1 and WB2 in closed form.
2. User to specify full path to two workbooks in two parts a) File directory path and b) File name (Graphic User Interface like forms with or without REFEDIT mode ie GUI)
3. User to specify Sheet names of two workbooks WS1 and WS2 in GUI. If sheet does not exist in target work book then new sheet is to be added.
2. Data transfer situation is mentioned below.
a) User specified Contiguous range such as B15:W300 in WB1 on user specified sheet to be posted at user specified starting location in user specified worksheet in WB2 such as "G15"
b) user specified copy pasting mode for values only or data with formulas mode in GUI.
I am open to consider your view point on this reduced scope.
If I reduce the work scope considerably then would you like to look into. My reduced requirement is outlined below.
I would like to have Excel -VBA code for the following scenario. I have Excel-2007 hence code should be compatible. I do not want vb.net or Visual Basic Development environment solution. I require excel resident Visual Basic Editor solution.
1. There are 2 workbooks WB1 and WB2 in closed form.
2. User to specify full path to two workbooks in two parts a) File directory path and b) File name (Graphic User Interface like forms with or without REFEDIT mode ie GUI)
3. User to specify Sheet names of two workbooks WS1 and WS2 in GUI. If sheet does not exist in target work book then new sheet is to be added.
2. Data transfer situation is mentioned below.
a) User specified Contiguous range such as B15:W300 in WB1 on user specified sheet to be posted at user specified starting location in user specified worksheet in WB2 such as "G15"
b) user specified copy pasting mode for values only or data with formulas mode in GUI.
I am open to consider your view point on this reduced scope.
Hi Sunil
The scope reduction you've suggested is a good start but the majority of the effort is still there.
From what I am reading you want a separate workbook containing code which presents a single form offering:
a) A method of selecting a SOURCE and TARGET folder/filename
b) A way of selecting a SOURCE and TARGET SHEET within each
c) A new sheet to be created in the TARGET if needed
d) a section of the dialog to specify source and target ranges for a copy with options to Paste Values or Paste data and formulas.
You have removed variations on item (d) but the additional copy/paste options are relatively easy once the first one is in place.
The majority of the effort is in building the form and then validating the ranges:
for example: you have not said if the user wants to see the sheets they are working with - for example: how do they know the source B15:W300 rather than B22:W322 and G15 for a target rather than F15?. (NB: if its always the same then they don't need to specify!)
Normally this would be done by selecting the cells within the sheets so the form has to handle opening the workbooks, showing the relevant pages and collecting the range that's selected before triggering the copy/paste. But if the user is going to select them anyway why not just do the copy/paste instead of returning control to the form?
None of these individual steps are particularly difficult but adding them all together and wrapping it up in a way that is user friendly and robust will probably take (me) a full day, others would probably be quicker.
Your best hope is that someone already has something close they are willing to share so I suggest marking the question for attention and asking the moderator to target additional experts
The scope reduction you've suggested is a good start but the majority of the effort is still there.
From what I am reading you want a separate workbook containing code which presents a single form offering:
a) A method of selecting a SOURCE and TARGET folder/filename
b) A way of selecting a SOURCE and TARGET SHEET within each
c) A new sheet to be created in the TARGET if needed
d) a section of the dialog to specify source and target ranges for a copy with options to Paste Values or Paste data and formulas.
You have removed variations on item (d) but the additional copy/paste options are relatively easy once the first one is in place.
The majority of the effort is in building the form and then validating the ranges:
for example: you have not said if the user wants to see the sheets they are working with - for example: how do they know the source B15:W300 rather than B22:W322 and G15 for a target rather than F15?. (NB: if its always the same then they don't need to specify!)
Normally this would be done by selecting the cells within the sheets so the form has to handle opening the workbooks, showing the relevant pages and collecting the range that's selected before triggering the copy/paste. But if the user is going to select them anyway why not just do the copy/paste instead of returning control to the form?
None of these individual steps are particularly difficult but adding them all together and wrapping it up in a way that is user friendly and robust will probably take (me) a full day, others would probably be quicker.
Your best hope is that someone already has something close they are willing to share so I suggest marking the question for attention and asking the moderator to target additional experts
ASKER
Would you be willing to consider if I reduce the scope still further as defined below.
I would like to have Excel -VBA code for the following scenario. I have Excel-2007 hence code should be compatible. I do not want vb.net or Visual Basic Development environment solution. I require excel resident Visual Basic Editor solution.
1. There are 2 workbooks WB1 and WB2 in open form to be opened by user
2. User to specify Sheet names of two workbooks WS1 and WS2 in GUI as well as Ranges mentioned below.Sheets exists in the workbooks.
3. Data transfer situation is mentioned below.
--User specified Contiguous range such as B15:W300 in WB1 on user specified sheet to be posted at user specified starting location in user specified worksheet in WB2 such as "G15" in values only.
If you are still unwilling then I shall mark the question for attention and asking the moderator to target additional experts.
Thanks for your kind indulgence. I am slightly weak on form interface part as such I wish to have starting point. I hope I shall be able to manage other points on my own apart from seeking help for my problem issues from Expert Exchange. Frankly I have recently joined and I am not aware if the reward points can be suitably increased commensurate to efforts.
I would like to have Excel -VBA code for the following scenario. I have Excel-2007 hence code should be compatible. I do not want vb.net or Visual Basic Development environment solution. I require excel resident Visual Basic Editor solution.
1. There are 2 workbooks WB1 and WB2 in open form to be opened by user
2. User to specify Sheet names of two workbooks WS1 and WS2 in GUI as well as Ranges mentioned below.Sheets exists in the workbooks.
3. Data transfer situation is mentioned below.
--User specified Contiguous range such as B15:W300 in WB1 on user specified sheet to be posted at user specified starting location in user specified worksheet in WB2 such as "G15" in values only.
If you are still unwilling then I shall mark the question for attention and asking the moderator to target additional experts.
Thanks for your kind indulgence. I am slightly weak on form interface part as such I wish to have starting point. I hope I shall be able to manage other points on my own apart from seeking help for my problem issues from Expert Exchange. Frankly I have recently joined and I am not aware if the reward points can be suitably increased commensurate to efforts.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Regmigrant,
Thanks for providing pointers. I shall try it out.
Thanks for providing pointers. I shall try it out.
I've requested that this question be closed as follows:
Accepted answer: 500 points for regmigrant's comment #a40986659
for the following reason:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Accepted answer: 500 points for regmigrant's comment #a40986659
for the following reason:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
ASKER
There are pointers to the question posed. Accepted for detailed working at our end.
Even if that is not acceptable the 'ask' here is way more time than I am willing to provide for free but if you try yourself and post specific questions you will get all the responses you need.