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