StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POUsing buttons in excel to set filters automatically in a pivot table
text
Body
copied!<p>Is there a fast macro that could automatically set filters in a pivot table for me if I were to press a form control button in excel? I don't have any experience with vba and macros and I tried recording one but it errors when one of the pivots isn't set to the same filters as it was originally set to when I recorded the macro.</p> <p>Here's the code for it:</p> <pre><code> Sub Filter_Foot() ' ' Filter_Foot Macro ' Application.ScreenUpdating = False ActiveSheet.PivotTables("PivotTable3").PivotFields("MD").CurrentPage = "(All)" With ActiveSheet.PivotTables("PivotTable3").PivotFields("MD") .PivotItems("Name 1").Visible = False .PivotItems("Name 2").Visible = False .PivotItems("Name 3").Visible = False .PivotItems("Name 4").Visible = False .PivotItems("Name 5").Visible = False .PivotItems("Name 6").Visible = False .PivotItems("Name 7").Visible = False .PivotItems("Name 8").Visible = False .PivotItems("Name 9").Visible = False .PivotItems("Name 10").Visible = False .PivotItems("Name 11").Visible = False .PivotItems("Name 12").Visible = False .PivotItems("Name 13").Visible = False .PivotItems("Name 14").Visible = False .PivotItems("Name 15").Visible = False .PivotItems("Name 16").Visible = False .PivotItems("Name 17").Visible = False .PivotItems("Name 18").Visible = False .PivotItems("Name 19").Visible = False .PivotItems("Name 20").Visible = False .PivotItems("Name 21").Visible = False .PivotItems("(blank)").Visible = False End With ActiveSheet.PivotTables("PivotTable2").PivotFields("MD").CurrentPage = "(All)" With ActiveSheet.PivotTables("PivotTable2").PivotFields("MD") .PivotItems("Name 1").Visible = False .PivotItems("Name 2").Visible = False .PivotItems("Name 3").Visible = False .PivotItems("Name 4").Visible = False .PivotItems("Name 5").Visible = False .PivotItems("Name 6").Visible = False .PivotItems("Name 7").Visible = False .PivotItems("Name 8").Visible = False .PivotItems("Name 9").Visible = False .PivotItems("Name 10").Visible = False .PivotItems("Name 11").Visible = False .PivotItems("Name 12").Visible = False .PivotItems("Name 13").Visible = False .PivotItems("Name 14").Visible = False .PivotItems("Name 15").Visible = False .PivotItems("Name 16").Visible = False .PivotItems("Name 17").Visible = False .PivotItems("Name 18").Visible = False .PivotItems("Name 19").Visible = False .PivotItems("Name 20").Visible = False .PivotItems("Name 21").Visible = False .PivotItems("(blank)").Visible = False End With ActiveSheet.PivotTables("PivotTable4").PivotFields("MD").CurrentPage = "(All)" With ActiveSheet.PivotTables("PivotTable4").PivotFields("MD") .PivotItems("Name 1").Visible = False .PivotItems("Name 2").Visible = False .PivotItems("Name 3").Visible = False .PivotItems("Name 4").Visible = False .PivotItems("Name 5").Visible = False .PivotItems("Name 6").Visible = False .PivotItems("Name 7").Visible = False .PivotItems("Name 8").Visible = False .PivotItems("Name 9").Visible = False .PivotItems("Name 10").Visible = False .PivotItems("Name 11").Visible = False .PivotItems("Name 12").Visible = False .PivotItems("Name 13").Visible = False .PivotItems("Name 14").Visible = False .PivotItems("Name 15").Visible = False .PivotItems("Name 16").Visible = False .PivotItems("Name 17").Visible = False .PivotItems("Name 18").Visible = False .PivotItems("Name 19").Visible = False .PivotItems("Name 20").Visible = False .PivotItems("Name 21").Visible = False .PivotItems("(blank)").Visible = False End With ActiveSheet.PivotTables("PivotTable1").PivotFields("MD").CurrentPage = "(All)" With ActiveSheet.PivotTables("PivotTable1").PivotFields("MD") .PivotItems("Name 1").Visible = False .PivotItems("Name 2").Visible = False .PivotItems("Name 3").Visible = False .PivotItems("Name 4").Visible = False .PivotItems("Name 5").Visible = False .PivotItems("Name 6").Visible = False .PivotItems("Name 7").Visible = False .PivotItems("Name 8").Visible = False .PivotItems("Name 9").Visible = False .PivotItems("Name 10").Visible = False .PivotItems("Name 11").Visible = False .PivotItems("Name 12").Visible = False .PivotItems("Name 13").Visible = False .PivotItems("Name 14").Visible = False .PivotItems("Name 15").Visible = False .PivotItems("Name 16").Visible = False .PivotItems("Name 17").Visible = False .PivotItems("Name 18").Visible = False .PivotItems("Name 19").Visible = False .PivotItems("Name 20").Visible = False .PivotItems("Name 21").Visible = False .PivotItems("(blank)").Visible = False End With ActiveSheet.PivotTables("PivotTable5").PivotFields("MD").CurrentPage = "(All)" With ActiveSheet.PivotTables("PivotTable5").PivotFields("MD") .PivotItems("Name 1").Visible = False .PivotItems("Name 2").Visible = False .PivotItems("Name 3").Visible = False .PivotItems("Name 4").Visible = False .PivotItems("Name 5").Visible = False .PivotItems("Name 6").Visible = False .PivotItems("Name 7").Visible = False .PivotItems("Name 8").Visible = False .PivotItems("Name 9").Visible = False .PivotItems("Name 10").Visible = False .PivotItems("Name 11").Visible = False .PivotItems("Name 12").Visible = False .PivotItems("Name 13").Visible = False .PivotItems("Name 14").Visible = False .PivotItems("Name 15").Visible = False .PivotItems("Name 16").Visible = False .PivotItems("Name 17").Visible = False .PivotItems("Name 18").Visible = False .PivotItems("Name 19").Visible = False .PivotItems("Name 20").Visible = False .PivotItems("Name 21").Visible = False .PivotItems("(blank)").Visible = False End With ActiveSheet.PivotTables("PivotTable6").PivotFields("MD").CurrentPage = "(All)" With ActiveSheet.PivotTables("PivotTable6").PivotFields("MD") .PivotItems("Name 1").Visible = False .PivotItems("Name 2").Visible = False .PivotItems("Name 3").Visible = False .PivotItems("Name 4").Visible = False .PivotItems("Name 5").Visible = False .PivotItems("Name 6").Visible = False .PivotItems("Name 7").Visible = False .PivotItems("Name 8").Visible = False .PivotItems("Name 9").Visible = False .PivotItems("Name 10").Visible = False .PivotItems("Name 11").Visible = False .PivotItems("Name 12").Visible = False .PivotItems("Name 13").Visible = False .PivotItems("Name 14").Visible = False .PivotItems("Name 15").Visible = False .PivotItems("Name 16").Visible = False .PivotItems("Name 17").Visible = False .PivotItems("Name 18").Visible = False .PivotItems("Name 19").Visible = False .PivotItems("Name 20").Visible = False .PivotItems("Name 21").Visible = False .PivotItems("(blank)").Visible = False End With ActiveSheet.PivotTables("PivotTable7").PivotFields("MD").CurrentPage = "(All)" With ActiveSheet.PivotTables("PivotTable7").PivotFields("MD") .PivotItems("Name 1").Visible = False .PivotItems("Name 2").Visible = False .PivotItems("Name 3").Visible = False .PivotItems("Name 4").Visible = False .PivotItems("Name 5").Visible = False .PivotItems("Name 6").Visible = False .PivotItems("Name 7").Visible = False .PivotItems("Name 8").Visible = False .PivotItems("Name 9").Visible = False .PivotItems("Name 10").Visible = False .PivotItems("Name 11").Visible = False .PivotItems("Name 12").Visible = False .PivotItems("Name 13").Visible = False .PivotItems("Name 14").Visible = False .PivotItems("Name 15").Visible = False .PivotItems("Name 16").Visible = False .PivotItems("Name 17").Visible = False .PivotItems("Name 18").Visible = False .PivotItems("Name 19").Visible = False .PivotItems("Name 20").Visible = False .PivotItems("Name 21").Visible = False .PivotItems("(blank)").Visible = False End With ActiveSheet.PivotTables("PivotTable8").PivotFields("MD").CurrentPage = "(All)" With ActiveSheet.PivotTables("PivotTable8").PivotFields("MD") .PivotItems("Name 1").Visible = False .PivotItems("Name 2").Visible = False .PivotItems("Name 3").Visible = False .PivotItems("Name 4").Visible = False .PivotItems("Name 5").Visible = False .PivotItems("Name 6").Visible = False .PivotItems("Name 7").Visible = False .PivotItems("Name 8").Visible = False .PivotItems("Name 9").Visible = False .PivotItems("Name 10").Visible = False .PivotItems("Name 11").Visible = False .PivotItems("Name 12").Visible = False .PivotItems("Name 13").Visible = False .PivotItems("Name 14").Visible = False .PivotItems("Name 15").Visible = False .PivotItems("Name 16").Visible = False .PivotItems("Name 17").Visible = False .PivotItems("Name 18").Visible = False .PivotItems("Name 19").Visible = False .PivotItems("Name 20").Visible = False .PivotItems("Name 21").Visible = False .PivotItems("(blank)").Visible = False End With ActiveSheet.PivotTables("PivotTable9").PivotFields("MD").CurrentPage = "(All)" With ActiveSheet.PivotTables("PivotTable9").PivotFields("MD") .PivotItems("Name 1").Visible = False .PivotItems("Name 2").Visible = False .PivotItems("Name 3").Visible = False .PivotItems("Name 4").Visible = False .PivotItems("Name 5").Visible = False .PivotItems("Name 6").Visible = False .PivotItems("Name 7").Visible = False .PivotItems("Name 8").Visible = False .PivotItems("Name 9").Visible = False .PivotItems("Name 10").Visible = False .PivotItems("Name 11").Visible = False .PivotItems("Name 12").Visible = False .PivotItems("Name 13").Visible = False .PivotItems("Name 14").Visible = False .PivotItems("Name 15").Visible = False .PivotItems("Name 16").Visible = False .PivotItems("Name 17").Visible = False .PivotItems("Name 18").Visible = False .PivotItems("Name 19").Visible = False .PivotItems("Name 20").Visible = False .PivotItems("Name 21").Visible = False .PivotItems("(blank)").Visible = False End With End Sub </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload