inserting a sheet into multiple workbooks

[Guest]

I have 500+ identical single-sheet workbooks in a folder and would like to
insert a sheet from another workbook into all of them. Is there a way to do
this in a batch w/o have to open each one and copying the sheet? Thanks.

spence

 

[Guest]

Hi Spence,
If you are familiar with VBA you can write a module to what you are asking.
The DIR function is very useful. You can load an array with all the excel
workbook path+names in a given folder i.e.:

MyFile = Dir(MyPath + "\*.xls")
' Load an array with all .xls file names found in the given path
aFileArray(1) = MyPath + "\" + MyFile
For i = 2 To 500
MyFile = Dir
If MyFile <> "" Then
aFileArray(i) = MyPath + "\" + MyFile
ElseIf MyFile = "" Then
Exit For
End If
Next i

Then recursively open each one and paste your worksheet. Regards.

 

[Ron de Bruin]

Hi spence

Try this one
http://www.rondebruin.nl/copy4.htm