Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy sheets to New Workbook - Excel 2007
Hi
I am trying to copy 3 of the 4 sheets in my Excel Workbook to a new workbook, save the new work book without the macro and close the original workbook (containing the source sheets) without saving. I keep getting the following error message on the copy sheet code, even when I record copying the sheets to a new workbook: Run time error '1004' Copy method of Sheets class failed Sub PrepareWB() Dim Sourcewb As Workbook Set Sourcewb = ActiveWorkbook Application.ScreenUpdating = False MsgBox "A macro to create your workbook is about to run", , "Macro Running" Sourcewb.Sheets(Array("Sale Data 3 Months", _ "3 Year Bus Plan", "Competitor Analysis")) _ .Copy ActiveWorkbook.SaveAs Filename:= _ "Chocolates Supreme Dashboard.xlsx", _ FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False ---more code--- ActiveWorkbook.Save Windows("Choclates Supreme.xlsm").Close SaveChanges = False Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy sheets to New Workbook - Excel 2007
On May 30, 4:13*pm, wrote:
Hi I am trying to copy 3 of the 4 sheets in my Excel Workbook to a new workbook, save the new work book without the macro and close the original workbook (containing the source sheets) without saving. *I keep getting the following error message on the copy sheet code, even when I record copying the sheets to a new workbook: Run time error '1004' Copy method of Sheets class failed *Sub PrepareWB() *Dim Sourcewb As Workbook * * Set Sourcewb = ActiveWorkbook * * Application.ScreenUpdating = False * * MsgBox "A macro to create your workbook is about to run", , "Macro Running" * * Sourcewb.Sheets(Array("Sale Data 3 Months", _ * * * * "3 Year Bus Plan", "Competitor Analysis")) _ * * * * .Copy * * ActiveWorkbook.SaveAs Filename:= _ * * * * "Chocolates Supreme Dashboard.xlsx", _ * * * * FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False ---more code--- * * ActiveWorkbook.Save * * Windows("Choclates Supreme.xlsm").Close SaveChanges = False * * Application.ScreenUpdating = True End Sub Thanks - I've run it now without errors |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you copy saved excel sheets to a new workbook | Excel Discussion (Misc queries) | |||
How do you copy colour from one workbook to another in Excel 2007 | Excel Discussion (Misc queries) | |||
Copy a graph within a workbook in Excel 2007 | Charts and Charting in Excel | |||
Copy Data from Excel 2007 XLSM workbook to Excel 2003 XLS workbook using ADO | Excel Programming | |||
why i cant copy sheets in excel workbook | Excel Discussion (Misc queries) |