CODEWITHSUNDEEP
excelvba
Nic
Nic

Reputation: 186

Copy worksheets from a master workbook to active workbook

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".

Upvotes: 0

Views: 85

Answers (1)

Eugene Astafiev
Eugene Astafiev

Reputation: 49395

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.

Upvotes: 1

Related Questions