asked on

Script or Macro to copy workbook to multiple workbooks Excel

Hi,

I have a total of 100 workbooks in a folder and sub folders. I would like to be able to add another workbook as a new tab to each of the 100 workbooks mentioned previously. Is the best way to do this using a VB Script? Or would a macro be more suitable for this task. I have attempted to write a script to do it but having little experience, this is beyond me. I have tried to write a script that will reference a list containing the files required to be opened (100 workbooks). Once opened the script will add the existing worksheet as a new tab. Alas, I am unable to do so.

Does anyone have a script that can get me started or any pointers would be appreciated?

Thanks in advance

ASKER CERTIFIED SOLUTION

Carlos Ramirez

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Carlos Ramirez

It's late here - forgot to tell you - a reference to "Microsoft Scripting Runtime" is required to work with the FileSystemObject.

Please add this in the Macro IDE (Alt+F11) by selecting - Tools - References "Microsoft Scripting Runtime"

Thanks

Carlos Ramirez

>> Is the best way to do this using a VB Script? Or would a macro be more suitable for this task.

Either way really would work. I use Excel in this solution so that I can "see" what I'm doing with the immediate window. Also since you already have an instance of Excel opened, you can leverage the objects easier.

Thanks

Carlos Ramirez

One last note - make sure the file containing this macro is not in the set being processed. I made that mistake and the program halted gracefully.

I named the worksheet g:\MasterTemplateArray.xlsm and processed the files in g:\Spreadsheets.

Thanks.

Mattywerts

ASKER

Exactly what I was looking for. Thanks heaps