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

 

[shg]

Just save the worksheet you're now copying as a single-sheet workbook.

Excel has a long-standing bug that if you try to copy a sheet too many times, it gets tired. See Copying worksheet programmatically causes run-time error 1004 in Excel

 

[Gti182]

thats quite an interesting link but i think it refers to a loop which my code doesn't have? Code used does save it as a single sheet workbook also.

still scratching my head at this one...

 

[xenou]

You might need to post the rest of the code you are using. Nothing in what you have posted suggests any kind of error should occur. It's not clear what you mean when you say you "run it a second time". Clearly it would work if you closed all your excel files, opened up the workbook, and run it again. So there is something in this "running a second time" that needs to be clarified.

 

[Gti182]

Full code below.

To clarify running a second time: The master file (file containing below code) remains open at all times.
Once the macro is run it creates Project2.xlsx file (modified/filtered version of the current tab in the master file) and saves it to a specific location on the network.

I can run below macro over and over without problem. The error only occurs when i run the macro (creates Project2.xlsx file), open and close this file, try rerun the macro.

To note: this problem doesn't occur if i save the Master file before rerunning the macro after opening/closing Project2.xlsx.

hope its a bit clearer now.

Sub Export2t()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
Application.AskToUpdateLinks = False
      
SPath = "W:\17.0 Projects\12.14_Projects" ' Save Path of report

Sheets("Projects").Copy

Dim wb1 As Workbook

Range("N2:AB363").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
    ActiveWindow.LargeScroll ToRight:=-1
    Application.GoTo Cells(1, 1)
    
Set wb1 = ActiveWorkbook

ActiveSheet.Shapes("Button 1").Delete
ActiveSheet.Shapes("Button 2").Delete
ActiveSheet.Shapes("Button 3").Delete
        
Range("N:O,Q:BI").Delete

Dim MonthsAhead
MonthsAhead = InputBox("Please enter the number of months ahead to be displayed:", , 3)
If Not IsNumeric(MonthsAhead) Then MonthsAhead = 3

Range("P1").formula = "=EDate(TODAY()," & MonthsAhead & ")"
Range("Q1").formula = "=TODAY()"
    
Range("P1:Q1").Select
Selection.NumberFormat = "General"
ActiveSheet.Range("P1:Q1").Font.Color = vbWhite
    
ActiveSheet.Range("A1:N500").AutoFilter Field:=14, Criteria1:= _
 ">" & Range("Q1").Value, Operator:=xlAnd, Criteria2:="<" & Range("P1").Value
 
ActiveWorkbook.Worksheets("Projects").AutoFilter.Sort. _
SortFields.Clear
ActiveWorkbook.Worksheets("Projects").AutoFilter.Sort. _
SortFields.Add Key:=Range("N1:N500"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Projec").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
End With
 
Application.GoTo Cells(1, 1)

Rows("1:1").RowHeight = 55

ActiveWorkbook.SaveAs FileName:=SPath & "\" & "Export2" & ".xlsx", FileFormat:=51

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

 

[xenou]

Okay. Well, I don't see anything at all that would cause this problem. It may be related to that bug shg mentioned, after all, even though it's not in a loop. You could maybe work around by adding a new workbook and copying the data from the sheet to it, rather than copying the sheet itself.