Error 1004 - Copy method of Worksheet class failed

[sharky12345]

Hi guys,

I'm having an issue trying to copy sheets to a new workbook - this works the first time I run it but fails every time after that, showing Error 1004 - Copy method of Worksheet class failed.

The full code is here;

Sub SaveFile()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Sheets("DATA").Activate
Sheets("SUMMARY").Activate

Set sourceWs = ThisWorkbook.Sheets("SUMMARY")
'Copy the source worksheet to a new workbook
sourceWs.Copy
'Get a reference to the new workbook
Set newWb = ActiveWorkbook

ThisWorkbook.Sheets("DATA").Copy After:=newWb.Sheets("SUMMARY")
newWb.Sheets("SUMMARY").Activate

SaveFolder = ThisWorkbook.Path
ActiveWorkbook.SaveAs Filename:=SaveFolder & "\" & "Fail Data_" & Format(Now, "dd_mm_yy") & ".xlsx", FileFormat:=51
                                   
FilePath = Thisworkbook.Path
ActiveWorkbook.SaveAs FilePath & "\" & "Fail Data_" & Format(Now, "dd_mm_yy") & ".xlsx", FileFormat:=51

newWb.Save
newWb.Close False
Set newWb = Nothing

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

And the line that fails is this one;

ThisWorkbook.Sheets("DATA").Copy After:=newWb.Sheets("SUMMARY")

Can anyone give me a clue what's going on?

 

[yoseeeph]

To fix error 1004 in Excel, try checking and correcting any VBA code mishaps, removing problematic add-ins, or deleting the 'GWXL97. XLA' file. If those don't work, creating a fresh Excel template or using a file repair tool could do the trick.