Copy all sheets from one workbook to the end of another workbook

[cory0789]

Anyone have a macro to copy each sheet of a workbook to the end of a target workbook. Note the copy workbook is .xls the target workbook is .xlsm... Thanks for all help. The copy workbook can be .xlsm if needed no big deal I just need to copy all 40 sheets to another workbook.

Thanks

[:) Sixthsense :)]

Sub CopySheets()
Dim Ws As Worksheet

Application.ScreenUpdating = False

With Workbooks("YourPasteWorkbookNameHere")
    For Each Ws In ThisWorkbook.Worksheets
        Ws.Copy After:=.Sheets(.Sheets.Count)
    Next Ws
End With

Application.ScreenUpdating = True

End Sub

[cory0789]

I get subscript out of range error...

I also get a chart not fit for dimensions error or something like that...

WHY!!!!! haha this is driving me crazy

[HaHoBe]

Hi, cory0789,

for the out of range error: both workbooks need to be open and try adding the extension

With Workbooks("YourPasteWorkbookNameHere.xlsm")

Ciao,
Holger

[Norie]

Do you have a chart sheet in the workbook you are copying from?

[cory0789]

Yes the sheets I am copying have charts several of them do!!! this is why I am getting the error but how do I fix it??? I dont understand why it wont just copy the sheet over even if it does have the charts...???

Thanks for all the help

[Norie]

I can only think there is some sort of compatibility issue involving the chart sheets.

If you save the source workbook as xlsm do you lose anything?

[cory0789]

nope but it still wont transfer over... there has to be a trick to it.