Solved: need to copy data from one workbook to another on selected cells

[zebradoby]

Need to copy data from one workbook to another on selected cells

'I want to create the macro with the following:
'1. Click Run
'2. user gets a prompt to select excel files to be processed
'3. User selects multiple files to be processed
'4. The macro shall copy and paste the data into a destination template and then save the file same as cell B3 in source file. There shall be a separate file created and saved for each source file.
'5. The data that needs to be copied from each worksheet is listed under Run button on Test
'6. The macro shall create a file for each file selected and then save it into a defined path. We can go with C:\.

Test1 - excel macro is stored, Test2 - source excel, Test 3 Destination excel.

'I have included Test1, Test2 and Test3.
'Can anyone please help ? i started working on it, but do not know how to go further as i keep getting error message 'on selecting files. And i deleted the code after that. And i have multiple of these source files that i want to process.

[rory]

Re your point 5, will the data always be the same, or do you want to be able to add to or change the ranges on the worksheet and have the code pick it up? (Basically I want to know if we can hardcode the data into the code). Also, does the picture box in the source workbook always have the same name, or is it the only picture, or do we have to use the location of it?

[zebradoby]

We need to capture the data in cells for point 5 as described in file Test1 under Run button on the attached file. I have multiple source files that will create same number of destination files at predefined location. So, code should pick it up, i think.

Picture- location is better. As i might have upto 6 pics in one worksheet. Thanks for your help. Hopefully this clarified.

[zebradoby]

The data shall be captured in the set template (Test3 in attached file)

[zebradoby]

Hi Rory
There are whole bunch of other cells that need to be copied. Once i have the base code, i will make those changes myself. I did not mention that huge list. And gave a few cells so as to get a base code. So, it cannot be hard coded. We need code 2 pick it up.
Thanks

[zebradoby]

http://www.tek-tips.com/viewthread.c...1424408&page=1

Hi i posted here also, but no response at all. Can you please help???Greatly appreciated.

[rory]

Will the additional ranges to be copied be laid out in the same way, or will they form a named range that can be used to determine where to get the information from?
(PS Please bear in mind that I am not in the same time zone as you! )

[zebradoby]

Thats ok..your help is greatly appreciated.

They will be all part of the template. We are going to leave the named ranges untouched as in the template or in the source file. We only want to copy some cells to another template and then save the template. I have 100 source files. So, i will create 100 destination file programmatically. I will keep the template intact though.

[zebradoby]

i meant we only want to copy the values in some cells from source to destination files.
I hope this clarified

[rory]

I got that. My point was that you have some cells in the Test1 workbook that determine which cells get copied from the source workbook to which cells in the template. Are you planning to name the cells in Test1 so that the code can just loop through all the cells in the named range to determine all the cells it needs to copy? It's not important just now, but I thought I'd ask so it can be incorporated at the outset.

[zebradoby]

No, we are not going to give them any names. This is the source file that we are using. No changes to be done.

[zebradoby]

Hi
Just to clarify, In the attached zip file:
Test1---Will store the macro
Test2---Source File (may or may not be multiple source files)
Test3---Destination template (number of destination files shall be same as source files)

[rory]

This should get you started. I changed the layout of your copy/paste table to make life easier.

[zebradoby]

Thanks Rory for your help. Greatly appreciated. I will make changes that i need. Will let you know if i need your help again. Thanks once again. Where are you in Scotland? I live in Dallas, Texas, USA.

[zebradoby]

Hi "Rory"
I am a beginner at VBA. Please let me know:
The code breaks down at " Set wksSource = mwbInvoice.Worksheets(rngCell.Value)" . Get Run Time error 9, subscript out of range. It lets me select the input files and when I select them, I get this run time error. If you have to change mwbInvoice to anything, feel free. I was trying to put code together, it was not working anyways.
For all cells that I need copied and pasted, I can list them on the external macro sheet. (Test1). ????
Thanks for your help and patience.

[rory]

That means that there is no worksheet in the selected workbook called "UW" or "Valuation" (depending on which row it's on).
Incidentally, I'm not in Scotland - I'm in England.

[zebradoby]

let me check

[zebradoby]

Hi Rory

It runs perfectly. I was able to make other changes as well. How do i keep the destination file name saved same as source file names?

Thanks

[zebradoby]

I get a run time error 70, (permission denied) when i try this. Code dies on this line If Dir(strNewPath) <> "" Then Kill strNewPath, on changing dest path as maFileName(lngIndex) thanks.

[rory]

Do you have full rights to the location where you are trying to save the files?