Copy worksheets from one workbook to another

[panman1]

I know very similar questions have previously been asked and I have tried to follow them without success. When I run this I get a Run-time error '1004' msg.

The 'CopySheets' Macro is saved in the 'ToWorkbook'

Thanks


Sub CopySheets()

Dim FromWorkbook As Workbook
Dim ToWorkbook As Workbook

Set FromWorkbook = Workbooks("FromWorkbook.xlsm")
Set ToWorkbook = Workbooks("ToWorkBook.xlsm")

Dim FromSheet As Worksheet

For Each FromSheet In FromWorkbook.Sheets
FromSheet.Copy After:=ToWorkbook.Sheets(ToWorkbook.Sheets.Count)
Next
End Sub

 

[Fluff]

What is the error message?

 

[DanteAmor]

Welcome to the forum!

Do you have any of the books protected, mainly ToWorkbook?
You have other macros in the sheets, it may be necessary to disable events. Try this:

Sub CopySheets()
  Dim FromWorkbook As Workbook
  Dim ToWorkbook As Workbook
  Dim FromSheet As Worksheet
  Set FromWorkbook = Workbooks("FromWorkbook.xlsm")
  Set ToWorkbook = Workbooks("ToWorkBook.xlsm")
[COLOR=#0000ff]  Application.EnableEvents = False[/COLOR]
  For Each FromSheet In FromWorkbook.Sheets
    FromSheet.Copy After:=ToWorkbook.Sheets(ToWorkbook.Sheets.Count)
  Next
[COLOR=#0000ff]  Application.EnableEvents = True[/COLOR]
End Sub

Another option to copy all sheets.

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

 

[panman1]

Thanks DanteAmor that now works!

 

[DanteAmor]

Thanks DanteAmor that now works!

I'm glad to help you. Thanks for the feedback.