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

On May 30, 4:13*pm, hgos...@worldonline.co.za 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