Script crashes - sometimes

[cowboysnut]

I have a script I have been using for years, but at one point it started crashing on me, but only sometimes. Usually it is when I open it fresh. If I run a few of the other macros in the workbook to sort of "prime the pump," it works just fine. I am using Excel 2016 for Mac. I'm trying to copy a sheet named "new," and paste it after another sheet already in the document (it's putting a sheet for a new individual in alphabetical order). I'm giving a little context in this script, but when it crashes, it crashes on the last line I am showing here. I cannot figure out why my code works sometimes but not others so I hope someone here can.

Error message: Run time error '1004': We couldn't copy this sheet.

        Dim AfterSheet As String
        Dim AfterFirst As String
        Dim AfterLast As String
        AfterFirst = ActiveCell.Offset(-1, 0).FormulaR1C1
        AfterLast = ActiveCell.Offset(-1, 1).FormulaR1C1
        AfterSheet = AfterFirst + "_" + AfterLast
        Sheets("new").Copy After:=Sheets(AfterSheet)

 

[rlv01]

Most likely variable is sometimes producting an invalid sheet name. You could try this.

    Dim AfterSheet As String
    Dim AfterFirst As String
    Dim AfterLast As String
    Dim WS As Worksheet
    AfterFirst = ActiveCell.Offset(-1, 0).FormulaR1C1
    AfterLast = ActiveCell.Offset(-1, 1).FormulaR1C1
    AfterSheet = AfterFirst + "_" + AfterLast

    On Error Resume Next
    Set WS = Worksheets(AfterSheet)
    On Error GoTo 0

    If WS Is Nothing Then
        MsgBox "'" & AfterSheet & "' is not a valid worksheet", vbOKOnly Or vbExclamation, Application.Name
        Worksheets("new").Copy After:=Worksheets(Worksheets.Count) 'copy to end
    Else
        Worksheets("new").Copy After:=Worksheets(AfterSheet)
    End If

 

[cowboysnut]

Thanks for the suggestion, but the result ended up being exactly the same. I never got the message box and it just went to the else statement, so it still crashed on essentially the same line. The AfterSheet variable properly defined and populated, but it just doesn't seem to like the command to paste the new sheet behind it.

 

[rlv01]

What if you just use this: Worksheets("new").Copy After:=Worksheets(Worksheets.Count) does it still crash?

    Dim AfterSheet As String
    Dim AfterFirst As String
    Dim AfterLast As String
    Dim WS As Worksheet
    
    AfterFirst = ActiveCell.Offset(-1, 0).FormulaR1C1
    AfterLast = ActiveCell.Offset(-1, 1).FormulaR1C1
    AfterSheet = AfterFirst + "_" + AfterLast


    With ActiveWorkbook
        On Error Resume Next
        Set WS = .Worksheets(AfterSheet)
        On Error GoTo 0

        If WS Is Nothing Then
            MsgBox "'" & AfterSheet & "' is not a valid worksheet. Macro abort", vbOKOnly Or vbExclamation, Application.Name
            Exit Sub
        Else
            WS.Copy After:=.Worksheets(.Worksheets.Count)    'copy to end
        End If
    End With

 

[cowboysnut]

That got it to run without crashing, but it copies AfterSheet instead of "new" and places it at the end instead of AfterSheet, the copy to end detail.

 

[rlv01]

and places it at the end

By design to take AfterSheet out of the equation to test that the copy mechanism is working, which it seems that it is.

but it copies AfterSheet instead of "new"...

That's my error and I can only say it was late in my timezone so my brain was a bit fuzzy. Try this instead.

    Dim AfterSheet As String
    Dim AfterFirst As String
    Dim AfterLast As String
    Dim WS As Worksheet
  
    AfterFirst = ActiveCell.Offset(-1, 0).FormulaR1C1
    AfterLast = ActiveCell.Offset(-1, 1).FormulaR1C1
    AfterSheet = AfterFirst + "_" + AfterLast
   
    With ActiveWorkbook
        On Error Resume Next
        Set WS = .Worksheets(AfterSheet)
        On Error GoTo 0
       
        If WS Is Nothing Then
            MsgBox "'" & AfterSheet & "' is not a valid worksheet. Macro abort.", vbOKOnly Or vbExclamation, Application.Name
            Exit Sub
        Else
              .Worksheets("new").Copy After:=WS
        End If
    End With

 

[rlv01]

If that does not work, then I'm not sure what is going on. Mac weirdness, maybe. A workaround you could try would be:

    Dim AfterSheet As String
    Dim AfterFirst As String
    Dim AfterLast As String
    Dim WS As Worksheet
   
    AfterFirst = ActiveCell.Offset(-1, 0).FormulaR1C1
    AfterLast = ActiveCell.Offset(-1, 1).FormulaR1C1
    AfterSheet = AfterFirst + "_" + AfterLast
    
    With ActiveWorkbook
        On Error Resume Next
        Set WS = .Worksheets(AfterSheet)
        On Error GoTo 0
        
        If WS Is Nothing Then
            MsgBox "'" & AfterSheet & "' is not a valid worksheet. Macro abort.", vbOKOnly Or vbExclamation, Application.Name
            Exit Sub
        Else
           .Worksheets("new").Copy After:=.Worksheets(.Worksheets.Count)    'copy to end
           .Worksheets(.Worksheets.Count).Move After:=WS
        End If
    End With

 

[cowboysnut]

I think I might have to chalk this up to Mac weirdness. When I delete the macro entirely and then paste it new with my original code or either of the codes you tried in your last 2 posts, save, close, and relaunch, it works perfectly. Save, close, and relaunch again and it bugs out. That isn't exactly a satisfactory answer, but it's the best I can come up with right now. Whenever I've tried this document on other computers - I think all PCs, it's worked perfectly.

So, it seems like if it thinks the code is new and has never been run, it works. And if I sufficiently prime the pump by running other macros, it works. And for a few years it always worked. But now, it crashes unless it thinks it is new or the pump is primed. I think it just might be my computer. I might be sharing this with others before too long, so I hoped to clean this bug out of it before then, but if it's just my computer that's buggy, I might have to live with it for now.