Copying worksheet to multiple workbooks

AsparagusLady

New Member
Joined
Dec 16, 2016
Messages
9
Hi all, very new to VB and would appreciate some help!

trying to copy a worksheet into 219 different workbooks. I've pilfered the code below from an old post (08) on this site but I'm getting 'Run-time error 9 : Subscript out of range'.
Can anyone help please? :)

---

Option Explicit
Public Sub CopySheetToAllWorkbooksInFolder()
Dim sourceSheet As Worksheet
Dim folder As String, filename As String
Dim destinationWorkbook As Workbook

'Worksheet in active workbook to be copied as a new sheet to the 219 workbooks

Set sourceSheet = ActiveWorkbook.Worksheets("16-17")

'Folder containing the 219 workbooks

folder = "\\ash-dc01\FolderRedirection\DHendricksen\Desktop\MacroTest\"

filename = Dir(folder & "*.xls", vbNormal)
While Len(filename) <> 0
Debug.Print folder & filename
Set destinationWorkbook = Workbooks.Open(folder & filename)
sourceSheet.Copy before:=destinationWorkbook.Sheets(1)
destinationWorkbook.Close True
filename = Dir() ' Get next matching file
Wend
End Sub
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,156
I am not sure about using UNC paths with Dir(), but the error would indicate that the worksheet is not really named "16-17", or, the wrong workbook is active. I would first check the name on the tab and see if anyone accidentally fat-fingered an errant space before/after.
 
Upvote 0

AsparagusLady

New Member
Joined
Dec 16, 2016
Messages
9
Thanks, that's worked!

Although it is now upset because the destination workbook contains more columns and rows than the source. Will try converting destination workbooks to .xlsm - I've heard that works.

Cheers - A
 
Upvote 0

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,156
Thanks, that's worked!

Although it is now upset because the destination workbook contains more columns and rows than the source. Will try converting destination workbooks to .xlsm - I've heard that works.

Cheers - A

Might you mean that the Source wb has more rows/columns than the destination wb's? Presuming this for the moment, how big is the used range in the source workbook? I ask, as rather than SaveAs 219 workbooks, it might be easier to copy the used range from the source wb/worksheet to a temp .xls file, and then copy from there.

Mark
 
Upvote 0

Forum statistics

Threads
1,191,621
Messages
5,987,727
Members
440,106
Latest member
davcurnutt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top