Share via

Pivot table macro

Anonymous
2019-08-20T02:42:01+00:00

I want to run this macro from any worksheet in my workbook but right now I have to be on the worksheet with the pivot table when I run this macro otherwise I get the error ‘Unable to get the PivotTables property of the Worksheet class’.  Can you help?

Sub ManyFilter()

Dim pf As PivotField

Set pf = ActiveSheet.PivotTables("PivotTable5").PivotFields("Month")

pf.ClearAllFilters

pf.EnableMultiplePageItems = True

pf.PivotItems("October").Visible = False

pf.PivotItems("November").Visible = False

pf.PivotItems("December").Visible = False

End Sub

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2019-08-20T07:29:29+00:00

Hi

You must refer the sheet name where pivot table 5 is.

Set pf = ThisWorkbook.Sheets("The name of your sheet").PivotTables("PivotTable5").PivotFields("Month")

Hope this helps you

Was this answer helpful?

1 person found this answer helpful.
0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-08-20T03:11:11+00:00

    Hi John, my name is Albanis

    This is why

    ActiveSheet.PivotTables("PivotTable5").........

    You are looking for "PivotTable5" on the ACTIVE sheet, but the table is on another. That is why the error

    You should refer to the sheet where the table is

    Let me know if it was helpful

    Regards

    Was this answer helpful?

    0 comments