Copy Worksheets To New Workbook

[James]

From an existing workbook I am trying to create a new workbook and copy all
worksheets (with the excpetion of the worksheet named "code") from the
workbook where the code is executing to the new workbook.
I keep receiving run time error 1004, application defined or object defined
error.

The code creates a new workbook, i.e. book1 but the following line fails:
ws.Copy after:=Workbooks(wbreport.Name).Sheets(wbreport.Sheets.Count)

Here is my code:
Private Sub cmdcopyworkbook_Click()

Dim wbcode As Workbook
Dim wbreport As Workbook

Dim ws As Worksheet

Set wbcode = ActiveWorkbook

Workbooks.Add

Set wbreport = Workbooks(Workbooks.Count)

For Each ws In wbcode.Worksheets

If ws.Name <> "code" Then
ws.Copy after:=Workbooks(wbreport.Name).Sheets(wbreport.Sheets.Count)
End If

Next ws

End Sub


Any help would be appreciated. Thanks.

 

[JLGWhiz]

You are probably getting the error on this line:

ws.Copy after:=Workbooks(wbreport.Name).Sheets(wbreport.Sheets.Count)

Change to:


ws.Copy after:=Workbooks(wbreport.Name).Sheets(Sheets.Count)

 

[Dave Peterson]

If wbreport is a workbook, then you can use:
ws.copy _
after:=wbreport.sheets(wbreport.sheets.count)

I didn't see anything that would cause the error, but maybe you could add a:

msgbox wbreport.name
before the .copy line. Maybe you're pointing to the wrong workbook?

Another way that doesn't rely on the position of the workbook:

Option Explicit
Private Sub cmdcopyworkbook_Click()

Dim wbCode As Workbook
Dim wbReport As Workbook
Dim ws As Worksheet

Set wbCode = ActiveWorkbook

Set wbReport = Workbooks.Add(1) 'single sheet in a new workbook

For Each ws In wbCode.Worksheets
If lcase(ws.Name) <> lcase("code") Then
With wbReport
ws.Copy _
after:=.Sheets(.Sheets.Count)
End With
End If
Next ws

if wbreport.sheets.count = 1 then
'do nothing
else
'remove that inital sheet
application.displayalerts = false
wbreport.worksheets(1).delete 'remove that inital sheet
application.displayalerts = true
end if

End Sub