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
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