Copy worksheets from a master workbook to active workbook

Question

The following code works sometimes but not always. I have looked at similar examples, but I need to work in a particular way (copy all worksheets from Master to active workbook).

I am trying to copy the worksheets from a 'Master' workbook ("MasterWorkbook.xlsm" spreadsheet) into my active workbook (an .xlsx file). I am running the macro from my active workbook. I have the 'Master' workbook open as well.

Sub CopySheetsFromMaster()
  Dim ToWorkbook As Workbook
  Set ToWorkbook = ActiveWorkbook
  Application.EnableEvents = False
  Workbooks("MasterWorkbook.xlsm").Worksheets.Copy After:=ToWorkbook.Sheets(ToWorkbook.Sheets.Count)
  Application.EnableEvents = True
End Sub

The code stops running at Line 5 "Workbooks("MasterWorkbook.xlsm").Worksheets.Copy...". I get the following error message "Run-time error '9': Subscript out of range".

You've got CopyToWorkbook defined at the start but on line 5 you're using something called ToWorkbook ... After:=ToWorkbook.Sheets(ToWorkbook.Sheets.Count) — jamheadart
Thanks for picking up, while an error in what I copied and pasted here, it's not the source of the error in Excel. I have now fixed code snippet above. — Nic

Eugene Astafiev Eugene Astafiev · Accepted Answer · 2020-06-04T11:10:27

It seems you need to open the workbook first. Use the Workbooks.Open method which opens a workbook.

Sub CopySheetsFromMaster()
  Dim ToWorkbook As Workbook
  Set ToWorkbook = ActiveWorkbook
  Application.EnableEvents = False

  Application.Workbooks.Open("path_to_your_workbook").Worksheets.Copy After:=ToWorkbook.Sheets(ToWorkbook.Sheets.Count)

  Application.EnableEvents = True
End Sub

Also, it makes sense to check the parameters you pass to the Worksheet.Copy method. Try to remove the After parameter. If it works correctly, then you need to pay special attention to the parameter.

Copy worksheets from a master workbook to active workbook

1 Answers