You are probably getting the error on this line:

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

Change to:


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




"James" wrote in message
...
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.Sh eets.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.Sh eets.Count)
End If

Next ws

End Sub


Any help would be appreciated. Thanks.