Changing VBA to run code without activating worksheet

[rjheibel]

I am trying to clean up my macros, which were mainly written through the recorder function. In doing this most of the actions were done through activating worksheets and performing commands. I have cleaned up most of the macro, but do not know how to correctly run the following without activating the sheet. This statement was written to add a blank column based on the values in column S. Thanks for your help.

    Sheets("Sub Activities-Step3").Select
    Application.DisplayAlerts = False
    With Range("S1", Range("S" & Rows.Count).End(xlUp))
        .Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(1), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
        .Offset(2, -1).SpecialCells(xlCellTypeConstants).Offset(, 1).ClearContents
        .Offset(, -1).EntireColumn.Delete
        .EntireColumn.RemoveSubtotal
    End With
    Application.DisplayAlerts = True

 

[Jeffrey Mahoney]

Without having to select the sheet

Sub Ha()

  Dim Sht As Worksheet

  Set Sht = Sheets("Sub Activities-Step3")
  Application.DisplayAlerts = False
  With Sht
    With .Range(.Range("S1"), .Range("S" & Rows.Count).End(xlUp))
      .Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(1), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
      .Offset(2, -1).SpecialCells(xlCellTypeConstants).Offset(, 1).ClearContents
      .Offset(, -1).EntireColumn.Delete
      .EntireColumn.RemoveSubtotal
    End With
  End With
  Application.DisplayAlerts = True
    
    
End Sub

 

[johnnyL]

Another option that would be just a bit shorter:

    Application.DisplayAlerts = False
        With Sheets("Sub Activities-Step3")
            With .Range(.Range("S1"), .Range("S" & Rows.Count).End(xlUp))
                .Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(1), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
                .Offset(2, -1).SpecialCells(xlCellTypeConstants).Offset(, 1).ClearContents
                .Offset(, -1).EntireColumn.Delete
                .EntireColumn.RemoveSubtotal
            End With
        End With
    Application.DisplayAlerts = True