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 |
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 |
All times are GMT +1. The time now is 10:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com