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?
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?
Rich (BB code):
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