Debug error help: Method 'Copy' of Object '_Worksheet' failed

[Gti182]

Hi all,

I'm having an odd issue with my below macro.

The macro copies a tab from a current file and then does some manipulation then save's it to a specified folder.
(note: some non relevant code has been omitted to keep it short)

The code works great on first try but if i execute the macro for the second time i get a debug error on red portion of the code "Method 'Copy' of Object '_Worksheet' failed"

Any way i can prevent this from happening?

Sub REPORTSG2()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
Application.AskToUpdateLinks = False
      
Dim wb1 As Workbook

SPath = "Z:\Template\" ' Save Path of report

Sheets("Sheet1").Copy

Set wb1 = ActiveWorkbook

ActiveSheet.Shapes("Button 1").Delete ' Delete macro buttons
ActiveSheet.Shapes("Button 2").Delete
        
ActiveWorkbook.SaveAs FileName:=SPath & "\" & "Test" & ".xlsx", FileFormat:=51 ' Define how to save file
wb1.Close
  
MsgBox "Process Complete! " & vbCrLf & "" & vbCrLf & "Files Saved in: " & vbCrLf & "" & vbCrLf & "" & SPath, vbInformation
' Message Box with path location of saved file

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.AskToUpdateLinks = True

End Sub

 

[Gti182]

anybody?

Its most likely just an error in my coding, i'm a bit of an amateur

 

[Gti182]

to add:

something i noticed. It only does this once i have opened the file which has been saved.

i.e. I can run the macro over and over and it works each time but as soon as i open and close the newly created file and then run the macro the above error occurs

 

[Gti182]

crosslinked here:
http://www.excelforum.com/excel-pro...-of-object-_worksheet-failed.html#post3637404

 

[xenou]

You're code has no workbook reference so it will assume the active workbook is the one to copy the sheet in. Maybe you need to activate the workbook with the code in it?

ThisWorkbook.Activate
Sheets("Sheet1").Copy

 

[Gti182]

no lucky with the code above xenou. This is really getting me down as i can't figure it out

 

[xenou]

It's mysterious. You may want to explain the procedure you follow when you run this code.

 

[Gti182]

i've received some good responses in my cross linked file which may help if you can see them.

Procedure:
Run macro to create new file. Process works 100% and can be run over and over without trouble.
However when i try rerun the macro after opening and closing the saved file the macro crashes with above error

 

[shg]

I suggest instead of copying the sheet you do sheets.add and specify a template:

Sheets.Add Type:="C:\myPath\myTemplate.xlsx" ' (or xlsm)

 

[Gti182]

thanks for the suggestion shg, i will try this. Can you elaborate on your post. what do you mean by template?