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
Sunil KakkarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

regmigrantCommented:
errr - what you seem to be asking for is a new skin for a lot of common excel functions when some user training would suffice.

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.
0
Sunil KakkarAuthor Commented:
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.
0
regmigrantCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Sunil KakkarAuthor Commented:
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.
0
regmigrantCommented:
Hi Sunil

sorry I won't be able to look at this until tomorrow but I if you want pointers to handling worksheets via forms here is a an earlier ee question that handles multiple sheets via a form interface:

Roy Cox user form

and another which looks at 'automating' copy/paste

flyster form to copy between sheets

The questions that get most attention are those where we are helping you solve a specific problem rather than creating a piece of work from scratch because that feels like we are doing your job rather than assisting someone who is trying on their own
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sunil KakkarAuthor Commented:
Hi Regmigrant,

Thanks for providing pointers. I shall try it out.
0
Martin LissOlder than dirtCommented:
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.
0
Sunil KakkarAuthor Commented:
There are pointers to the question posed. Accepted for detailed working at our end.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.