Excel vba macro crash on worksheet select - worked 1 week ago

Question

I have spent a lot of time here and elsewhere searching, and I haven't uncovered an answer. I have a spreadsheet created and used in Excel O365 I use to manage a backlog of testing to be done, and it also includes a tab to handle forecasting.

In two separate macros, the process of selecting a worksheet causes Excel to crash.

It behaves exactly as though the worksheets had been renamed in the file but not updated in the vba (an error I have previously made and learned the hard way) but nothing has been renamed, and as far as I can tell, nothing has changed at all for months. Yet, this started misbehaving about a week ago and I can't figure it out.

Example code that is failing:

Sub Send_Range_Or_Whole_Worksheet_with_MailEnvelope()
Dim AWorksheet As Worksheet
Dim Sendrng As Range
Dim rng As Range

'On Error GoTo StopMacro

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

'Fill in the Worksheet/range you want to mail
'Note: if you use one cell it will send the whole worksheet
Set Sendrng = ThisWorkbook.Sheets("Weekly Forecast").Range("A10:A78")

'Remember the activesheet
Set AWorksheet = ActiveSheet

With Sendrng
    ' Select the worksheet with the range you want to send
    .Parent.Select
    'Remember the ActiveCell on that worksheet
    Set rng = ActiveCell
    'Select the range you want to mail
    .Select
    ' Create the mail and send it
    ActiveWorkbook.EnvelopeVisible = True
    With .Parent.MailEnvelope
        ' Set the optional introduction field thats adds
        ' some header text to the email body.
        .Introduction = "See below for the weekly forecast. Thank you!"

         With .Item
            .To = "(redacted email address)"
            .Subject = "Weekly Forecast"
            .Send
        End With

    End With

    'select the original ActiveCell
    rng.Select
End With

'Activate the sheet that was active before you run the macro
AWorksheet.Select

StopMacro:
With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With
ActiveWorkbook.EnvelopeVisible = False

End Sub

The "Set Sendrng = " statement is where Excel crashed when stepping through this macro.

Example 2 is a poor man's log of some data where it is simply copied from one worksheet and pasted into another for purpose of a 'snapshot':

Sub RefreshAllData()

' Updated 28Nov2017: Added "add to backlog log" ability
' RefreshAllData Macro
' Turn off screen updating

Application.ScreenUpdating = False

' Refresh all data / queries
ActiveWorkbook.RefreshAll

'Calculate should update all pivot tables
Calculate

' Append latest backlog to the backlog log
Sheets("2.7").Select
Range("A60:D73").Select
Selection.Copy
Sheets("Backlog Log").Select
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
Sheets("2.7").Select
Range("A1").Select

' Resume screen updating
Application.ScreenUpdating = True
End Sub

The Sheets("2.7").Select statement is where Excel crashes.

Things I have tried:

1. You can see I've got one instance where the sheet select is direct, and one where it is a named range, and both fail.
2. I have attempted renaming of sheets and updating the code to reflect the new names, but those also fail.
3. I have reset macro security to force it to re-ask / re-enable macros, and this did not have an affect.

I'm at wit's end on this seemingly trivial issue, but these save enough manual time that I would really like to figure them out. Any help or pointers would be greatly appreciated.

Answer 1

You can try this for your first sub. Modify the MailMe range to your Forecast range.

Option Explicit

Sub ForeCast()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Weekly Forecast")

'Modify the below range to send
Dim MailMe As Range: Set MailMe = ws.Range("A10:A78")

Application.ScreenUpdating = False

With MailMe
    ThisWorkbook.EnvelopeVisible = True
    With MailEnvelope
        .Introduction = "See below for the weekly forecast. Thank you!"
             With .Item
                .To = "(redacted email address)"
                .Subject = "Weekly Forecast"
                .Send
            End With
    End With
End With

ThisWorkbook.EnvelopeVisible = False
Application.ScreenUpdating = True

End Sub

---

If the code is housed in the book in that holds your sheets, the below should work. Notice that you never need .Select or .Selection to move/add/change/delete a cell/range/sheet/book.

Sub RefreshAllData()

Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("2.7")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("Backlog Log")

Application.ScreenUpdating = False
    ThisWorkbook.RefreshAll
    ws1.Range("A60:D73").Copy
    ws2.Range("A" & ws2.Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValuesAndNumberFormats
Application.ScreenUpdating = True

End Sub