Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Worksheets To New Workbook
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Worksheets To New Workbook
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy all worksheets to another workbook, excl. duplicate sheets already in other workbook | Excel Programming | |||
Copy worksheets to a different workbook | Excel Programming | |||
Copy worksheets within a workbook | Excel Discussion (Misc queries) | |||
Copy Worksheets from one Workbook to Another | Excel Worksheet Functions | |||
Copy four worksheets from one workbook into a new workbook.e-mail | Excel Programming |