Copy Worksheets To New Workbook
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
James wrote:
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.
--
Dave Peterson
|