title: Excel vba macro crash on worksheet select - worked 1 week ago
body: <p>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.</p>
  
  <p>In two separate macros, the process of selecting a worksheet causes Excel to crash.</p>
  
  <p>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.</p>
  
  <p>Example code that is failing:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The "Set Sendrng = " statement is where Excel crashed when stepping through this macro.  </p>
  
  <p>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':</p>
  
  <pre><code>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" &amp; 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
  </code></pre>
  
  <p>The Sheets("2.7").Select statement is where Excel crashes.</p>
  
  <p>Things I have tried:</p>
  
  <ol>
  <li>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.  </li>
  <li>I have attempted renaming of sheets and updating the code to reflect the new names, but those also fail.</li>
  <li>I have reset macro security to force it to re-ask / re-enable macros, and this did not have an affect.</li>
  </ol>
  
  <p>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.</p>