Copying worksheet to multiple workbooks

[AsparagusLady]

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

 

[GTO]

When you step thru the code (F8), what line returns the error?

 

[AsparagusLady]

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


Thanks

 

[AsparagusLady]

Just in case - error occurs when tabbing from

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

to

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

I'm not sure if this means the problem is with the first or second of those lines (complete newbie, sorry!).

 

[GTO]

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.

 

[AsparagusLady]

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

 

[GTO]

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

 

[AsparagusLady]

That is what I meant, yes! Realised that as I started, however I found this lovely little gem https://www.experts-exchange.com/ar...-XLS-XLSX-XLSM-with-without-macro-option.html .....converted the files and it's all run through perfectly.

Thanks for your help and merry Christmas!

 

[GTO]

...

Thanks for your help and merry Christmas!

You are very welcome and Merry Christmas to you and yours as well