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
|