Need macro help filtering pivots

willm57

New Member
Joined
Sep 25, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I'm fairly new to macros, so my code isn't pretty. I'm trying to put the same pivot table in multiple sheets (already have a macro to create all the sheets and rename them). I have a macro that copies the pivot table into each named tab. The problem is, I can't get it to let me filter the pivot table within each sheet. I tried to record each filter individually, like the code below (there are 11 sheets for different people), but I keep getting an error (Run-time error '1004': Unable to get the PivotTables property of the Worksheet class) on the ClearAllFilters line. At this attempt I tried clearing all filters and resetting all of them, I really only need to reset the Party Responsible. I would really appreciate some help on this one. It's the last step in a long line of work for this workbook. Thank you!
Sub Macro43()
'
' Macro43 Macro

Sheets("V Coman").Select
Range("B1").Select
ActiveSheet.PivotTables("PivotTable190").ClearAllFilters
With ActiveSheet.PivotTables("PivotTable190").PivotFields("Aged Buckets")
.PivotItems("0-30 Days").Visible = False
.PivotItems("31-60 Days").Visible = False
.PivotItems("61-90 Days").Visible = False
End With
ActiveSheet.PivotTables("PivotTable190").PivotFields("Party Responsible"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable190").PivotFields("Party Responsible"). _
CurrentPage = "Valentia Coman"
End Sub
Sub Macro44()
'
' Macro44 Macro

Sheets("J Moore-V Coman").Select
Range("B1").Select
ActiveSheet.PivotTables("PivotTable189").ClearAllFilters
With ActiveSheet.PivotTables("PivotTable189").PivotFields("Aged Buckets")
.PivotItems("0-30 Days").Visible = False
.PivotItems("31-60 Days").Visible = False
.PivotItems("61-90 Days").Visible = False
End With
ActiveSheet.PivotTables("PivotTable189").PivotFields("Party Responsible"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable189").PivotFields("Party Responsible"). _
CurrentPage = "Jessica Moore/Valencia Coman"
End Sub
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Watch MrExcel Video

Forum statistics

Threads
1,114,525
Messages
5,548,556
Members
410,849
Latest member
DannyNg
Top