Excel: Views

Here's an example of some macros that can be assigned to a button. It calls up certain saved "Custom Views" (in this case called "All" and "Quantities"). It also shows how to do some filtering - basically these can be simply recorded using the macro recorder.

The two functions "RecordLocation" and "ReturnToLocation" allow the active cell, selection, and visible range to be around the same as before it was called.

Public currentRange As Range
Public TopRow As Integer
Public TopCol As Integer
Public currentSelection As Range
Sub RecordLocation()
    Set currentSelection = Selection
    TopRow = ActiveWindow.VisibleRange.Cells.Row
    TopCol = ActiveWindow.VisibleRange.Cells.Column
End Sub
Sub ReturnToLocation()
    currentSelection.Select
    ActiveWindow.ScrollRow = TopRow
    ActiveWindow.ScrollColumn = TopCol
End Sub
Sub All()
'
' All Macro
'
'
    RecordLocation
    ActiveWorkbook.CustomViews("All").Show
    ReturnToLocation
End Sub
Sub ShowQuantities()
'
' ShowQuantities Macro
'
'
    RecordLocation
    ActiveWorkbook.CustomViews("Quantities").Show
    ReturnToLocation
End Sub
Sub Baseline1()
'
' Baseline1 Macro
'
'
    ActiveSheet.Range("$A$3:$BE$507").AutoFilter Field:=51, Criteria1:="=1", _
        Operator:=xlOr, Criteria2:="="
End Sub
Sub Baseline0()
'
' Baseline0 Macro
'
'
    ActiveSheet.Range("$A$3:$BE$507").AutoFilter Field:=51, Criteria1:="=0", _
        Operator:=xlOr, Criteria2:="="
End Sub