Apply code in all pivot tables in the same worksheet

Hi Experts,

i found this code but I could not modify it so it would work on two pivot tables on the same sheet:

Set TargetDate = Range("A1")
Dim PI As PivotItem


For Each PI In ActiveSheet.PivotTables("PivotTable1").PivotFields("Target Due Date").PivotItems
If DateValue(PI.Name) < TargetDate Or DateValue(PI.Name) = TargetDate Then
PI.Visible = True
Else
PI.Visible = False
End If
Next PI

How do I get a code to work on at least two pivot tables on the same sheet?

I apologize if i do not know how to use code tags.

Thank you

You're missing a thing or two. There are two ways to point to a lot of things in VB. One is by what I like to call "array" like Sheet(1) or Sheet("SheetName"). Normally you address these items like:

For I = 1 to 10
    Sheets(I). dosomething
next

The other is by pointer or "object" which is what I will use here. These are addressed using For Each

For Each sh in ActiveWorkbook.Sheets

This should work for your purposes.

Public Sub Update_Pivot()
Dim sh As Worksheet         ' Worksheet containing the pivot table
Dim pt As PivotTable        ' Pivot table "object"
Dim pf As PivotField        ' Pivot field "object"
Dim pi As PivotItem         ' Pivot item "object"

' Set the sheet
Set sh = Sheets("YourSheetName")

' Loop through each pivot table on the sheet
For Each pt In sh.PivotTables
    ' Set the pivot field
    Set pf = pt.PivotFields("Target Due Date")
    
    ' You can't have all the pivot items false, so you have to turn on all of them
    ' then turn off the ones you don't use
    For Each pi In pf.PivotItems
        pi.Visible = True
    Next pi
    
    For Each pi In pf.PivotItems
        If DateValue(pi.Value) <= TargetDate Then
            pi.Visible = True
        Else
            pi.Visible = False
        End If
    Next pi
Next pt

End Sub

Hi,

Try changing

For Each PI In ActiveSheet.PivotTables("PivotTable1").PivotFields("Target Due Date").PivotItems

to

Dim PTCtr as double
For PtCtr = 1 to Activesheet.PivotTables.Count
PI In ActiveSheet.PivotTables(PTCtr).PivotFields("Target Due Date").PivotItems
'your other code
Next PTCtr