Run time error "9" when copy all sheets to a new workbook

**Apple Ling** · 07-05-2011, 05:13 AM

Hi,

I want to copy over the open workbook to a new template file but i got the run time "9" error.

Sub CopySheets()

    
    Dim WB As Workbook
    Dim WB2 As Workbook

    Set WB = ThisWorkbook

    Workbooks.Add Template:="C:\xxx.xltm"
    Set WB2 = Workbooks("xxx.xlsm")

    WB.Sheets.Copy Before:=WB2.Sheets(1)


End Sub

I hope someone can assist me to fix the error.

Thank you in advance.

**Apple Ling** · 07-06-2011, 05:42 AM

Anyone help please.

**watersev** · 07-06-2011, 07:41 AM

for Excel 2003 template workbook:

Sub test()
Application.ScreenUpdating = 0
With ActiveWorkbook: .Sheets.Copy: .SaveAs "C:\Temp.xlt": End With
Application.ScreenUpdating = 1
End Sub

**royUK** · 07-06-2011, 07:49 AM

maybe

Sub CopySheets()

    
    Dim WB As Workbook
    Dim WB2 As Workbook

    Set WB = ThisWorkbook

    Workbooks.Add Template:="C:\xxx.xltm"
    Set WB2 = ActiveWorkbook
    WB.Sheets.Copy Before:=WB2.Sheets(1)


End Sub