Update data from one workbook to another

**GoodOmens** · 10-08-2009, 11:04 AM

Hi,

I was able to grab an excellent macro here that allowed me to loop through worksheets in workbook and make a recap sheet.

However, I am now tasked with the project of figuring out how to update a workbook with data from an older workbook (basically update some fields that our auto-generated report doesn't do quite yet). The workbooks each contain around 200 sheets, each with the same name (IE each sheet in workbook 1 has the same sheet in workbook 2).

I need to loop through each worksheet in the old book (named 10000-10200) and copy certain cells to the new workbook (with worksheets named the same 10000-10200).

I'm currently using

Please Login or Register  to view this content.

To loop through all the sheets in my recap macro - I have a feeling I could use it again in the new macro - just instead of copying to a recap sheet I copy to another workbook.

Having a pop-up and asking the user which workbook they want to copy the data to would be a huge bonus.

Unfortunately I can't post examples because of sensitivity concerns, but if you guys really need something I could create a dummy sheet.

Thanks!

**jaslake** · 10-09-2009, 01:42 PM

The attached MacroBook has a procedure that I've used successfully in the past to cycle through files in a folder, match worksheet names and perform some action on the target workbook/worksheet. I must admit, I've viewed your post several times and have always come away wondering how you are going to identify those "certain cells" in each of 200 worksheets.

To run the procedure, you need to:
'********************************************************
'Reference to Microsoft Scripting Runtime must be set
'Tools->References-> select Microsoft Scripting Runtime
'********************************************************
You should note that the source and target files must be in the same folder. Also note that the procedure will select ALL Excel files in the folder unless specifically excluded in the procedure.
Hope this helps you. Let me know how you make out.
J