PDA

View Full Version : [SOLVED] Loop through all Pivot tables in a worksheet and filter visability VBA



garbour
01-25-2018, 03:09 PM
I have what I thought was to be a straightforward task to accomplish, but two days later I cannot figure out how to do this.

I've posted the simplest code of what I want to accomplish (for a single PivotTable). This code will turn off visibility of Name1-6 in Pivottable1. What I need the code to accomplish is to apply this filter to every PivotTable in my sheet (not workbook, only the activesheet), approximately 50 of them. Also, in there are instances where "Name2" or "Name4", etc. does not occur in a PivotTable. I would like the code to move on to the next name rather than throw an error.

Excel 2013



Sub filterPivot()

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Memo")
.PivotItems("Name1").Visible = False
.PivotItems("Name2").Visible = False
.PivotItems("Name3").Visible = False
.PivotItems("Name4").Visible = False
.PivotItems("Name5").Visible = False
.PivotItems("Name6").Visible = False
End With
End Sub


Thanks to anyone who can help me out!

Paul_Hossler
01-25-2018, 03:54 PM
I don't think I've ever heard of 50 pivot tables on a single sheet, but try this




Option Explicit

Sub filterPivot()
Dim pt As PivotTable

On Error Resume Next
For Each pt In ActiveSheet.PivotTables
With pt.PivotFields("Memo")
.PivotItems("Name1").Visible = False
.PivotItems("Name2").Visible = False
.PivotItems("Name3").Visible = False
.PivotItems("Name4").Visible = False
.PivotItems("Name5").Visible = False
.PivotItems("Name6").Visible = False
End With
Next
End Sub

garbour
01-25-2018, 04:48 PM
Hi Paul,

Thanks for your help.

I tried your code and it only worked on the first PT in the worksheet, which is PivotTable49.

Regards,

greg

Paul_Hossler
01-25-2018, 06:43 PM
Hmmm, should have worked (famous last words)

If you want, try this version

I added two Msgboxs and moved the On Error to which handles no pivot field named Memo and no PivotItem = Namex



Option Explicit

Sub filterPivot()

Dim pt As PivotTable

MsgBox ActiveSheet.PivotTables.Count

For Each pt In ActiveSheet.PivotTables

MsgBox pt.Name

On Error Resume Next
With pt.PivotFields("Memo")
.PivotItems("Name1").Visible = False
.PivotItems("Name2").Visible = False
.PivotItems("Name3").Visible = False
.PivotItems("Name4").Visible = False
.PivotItems("Name5").Visible = False
.PivotItems("Name6").Visible = False
End With
On Error GoTo 0
Next

End Sub

garbour
01-26-2018, 02:20 PM
Paul,

I figured it out... needed to add pt.update and pt.refresh.

Thanks for your help!


Sub filterPivot()

Dim pt As PivotTable

For Each pt In ActiveSheet.PivotTables

pt.RefreshTable
pt.Update


On Error Resume Next
With pt.PivotFields("Memo")
.PivotItems("Name1").Visible = False
.PivotItems("Name2").Visible = False
.PivotItems("Name3").Visible = False
.PivotItems("Name4").Visible = False
.PivotItems("Name5").Visible = False
.PivotItems("Name6").Visible = False

End With
On Error GoTo 0

Next

End Sub