VBA code workthrough, this way is causing run-time error

[cizzett]

OK so this has been working but now im getting a runtime error when it gets to this code.

Any advice? Greatly appreciated.

Sub Prep()

    ' Prep Macro
' Hide and prep columns for final save
'
      


    ' Resize and hide columns
    
    Columns("A:A").ColumnWidth = 5
    Columns("M:N").Select
    Selection.EntireColumn.Hidden = True
    Columns("W:X").Hidden = True
    Columns("T:T").ColumnWidth = 50.57




        Range("B3").Select
    ActiveWorkbook.Worksheets("Today").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Today").Sort.SortFields.Add Key:=Range("B3:B62"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "New,Waiting on response,Resolved,Non-Batch", DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Today").Sort
        .SetRange Range("A2:X62")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        
' Refresh data tables
    Application.DisplayAlerts = False
        
    
    ' Delete_Buttons Macro
'   This removed the VBA Buttons and hides row 1 on Todays data tab
    ActiveSheet.Shapes.Range(Array("ImportQLTYData")).Select
    Selection.Delete
    ActiveSheet.Shapes.Range(Array("Finalize")).Select
    Selection.Delete
    ActiveSheet.Shapes.Range(Array("EmailPrep")).Select
    Selection.Delete
    ActiveSheet.Shapes.Range(Array("LotWS")).Select
    Selection.Delete
    
    Rows("1:1").Select
    Selection.EntireRow.Hidden = True
    
    ' deletestuff Macro
'
    Sheets("Instruction Sheet").Select
    ActiveWindow.SelectedSheets.Visible = False
         
    Sheets("L.W.S.").Select
    ActiveWindow.SelectedSheets.Visible = False
         
    Sheets("E-mail").Select
    ActiveWindow.SelectedSheets.Visible = False
         
    Sheets("Today").Select
            Application.DisplayAlerts = False
    Range("C3").Select
        ' Filter out blanks on Pivot Tables
    
    Sheets("Pivot").Select
    
    ActiveWorkbook.RefreshAll
    
   ActiveSheet.PivotTables("PivotTable5").PivotFields("Legacy Item #"). _
        ClearAllFilters
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Legacy Item #")
        .PivotItems("(blank)").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("SOS").ClearAllFilters
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("SOS")
        .PivotItems("").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    
    ' Go back to Today Sheet
    Sheets("Today").Select
    Range("C3").Select
    ActiveWorkbook.RefreshAll
    
    
    
    End With






End Sub

 

[cizzett]

Ok so I have cleaned this up a bit more and now its working again but I think I need to re-write it with proper variables etc.
Here it is now

Sub Prep()

 ' Prep Macro
' Hide and prep columns for final save
'
      


    ' Resize and hide columns
    
    Columns("A:A").ColumnWidth = 5
    Columns("M:N").Hidden = True
    Columns("V:X").Hidden = True
    Columns("T:T").ColumnWidth = 50.57




        Range("B3").Select
    Sheets("Today").Sort.SortFields.Clear
    Sheets("Today").Sort.SortFields.Add Key:=Range("B3:B62"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "New,Waiting on response,Resolved,Non-Batch", DataOption:=xlSortNormal
    With Sheets("Today").Sort
        .SetRange Range("A2:X62")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        
' Refresh data tables
    Application.DisplayAlerts = False
        
    
    ' Delete_Buttons Macro
'   This removed the VBA Buttons and hides row 1 on Todays data tab
    Sheets("Today").Shapes.Range(Array("ImportQLTYData")).Delete
    Sheets("Today").Shapes.Range(Array("Finalize")).Delete
    Sheets("Today").Shapes.Range(Array("EmailPrep")).Delete
    Sheets("Today").Shapes.Range(Array("LotWS")).Delete
    
    Rows("1:1").Select
    Selection.EntireRow.Hidden = True
    
    ' deletestuff Macro
'
    Sheets("Instruction Sheet").Visible = False
    Sheets("L.W.S.").Visible = False
    Sheets("E-mail").Visible = False
             
             Application.DisplayAlerts = False
        
        ' Filter out blanks on Pivot Tables
    
Sheets("Pivot").Activate
    ActiveWorkbook.RefreshAll
    
   Sheets("Pivot").PivotTables("PivotTable5").PivotFields("Legacy Item #"). _
        ClearAllFilters
    With Sheets("Pivot").PivotTables("PivotTable5").PivotFields("Legacy Item #")
        .PivotItems("(blank)").Visible = False
    End With
    Sheets("Pivot").PivotTables("PivotTable1").PivotFields("SOS").ClearAllFilters
    With Sheets("Pivot").PivotTables("PivotTable1").PivotFields("SOS")
        .PivotItems("").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    
    ' Go back to Today Sheet
    Sheets("Today").Activate
    Range("C3").Select
    ActiveWorkbook.RefreshAll
    
    
    
    End With








End Sub