tlc53
Active Member
- Joined
- Jul 26, 2018
- Messages
- 399
Hi there,
Can someone help me fix this code please? Step 6 is the bit that's not quite right. Instead of showing the AutoFilter categories selected, I want it to hide category "ZZZSpare".
Thank you!
Can someone help me fix this code please? Step 6 is the bit that's not quite right. Instead of showing the AutoFilter categories selected, I want it to hide category "ZZZSpare".
Thank you!
VBA Code:
Sub Prepare()
'
' Prepare Macro
'
'1) Select table "Member_Table_Subtotal"
Application.Goto Reference:="Member_Table_Subtotal"
'2) Subtotal = Remove all
Selection.RemoveSubtotal
'3) Remove filters
Selection.AutoFilter
'4) Select table "Member_Table"
Application.Goto Reference:="Member_Table"
'5) Sort table by "Team Member" then by "Location"
ActiveWorkbook.Worksheets("Stocktake Print Sheets").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Stocktake Print Sheets").Sort.SortFields.Add2 Key _
:=Range("D6:D122"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Stocktake Print Sheets").Sort.SortFields.Add2 Key _
:=Range("C6:C122"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Stocktake Print Sheets").Sort
.SetRange Range("A5:G122")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'6) Add filter, Under title "Location" hide "ZZZSpare"
Selection.AutoFilter
ActiveSheet.Range("$A$5:$G$122").AutoFilter Field:=3, Criteria1:=Array( _
"Kitchen", "Lundia", "Stationery", "Storage Room", "Toilet"), Operator:= _
xlFilterValues
'7) Add subtotals
Selection.Subtotal GroupBy:=4, Function:=xlCount, TotalList:=Array(1), _
Replace:=False, PageBreaks:=True, SummaryBelowData:=True
ActiveWindow.SmallScroll Down:=-24
'8) Go to top of table
Range("A5").Select
End Sub