VBA - Iterating through report filter on TWO pivot tables

[Juiceboxjuke]

Hello! I'm brand new to learning VBA, and this forum has been a wonderful resource so far. Thank you in advance for your help.

I have two pivot tables from the same source data displaying different fields, but each pivot table has a report filter on the same field. That is, one pivot table displays age and gender and the other pivot table shows street address, but they both have a report filter on "name". (Details have been changed due to sensitive information - I cannot do this with one pivot table, though it may appear so from this description).

My final aim is to have a macro that iterates through the report filters (on "name") for both pivot tables at the same time, and then copies the results into a new sheet.

Thus, at the end, I should end up with X sheets, each of which shows the age, gender, and street address of an individual person.

I had gotten this to work with one pivot table, using modified code from this thread (2nd post by Jerry Sullivan).

However, when I try to do the same thing for two pivot tables, only one of the pivot tables iterates through the name filter, while the other pivot table remains stuck on the first name.

Here is the code for iterating through the report filters on the pivot tables - as you can see, I just repeated the same code for both pivot tables, changing the name of the pivot table.

Any ideas what might be the problem?

Dim sItem As String
Dim i As Integer

Application.ScreenUpdating = True
With ActiveSheet.PivotTables("AgeGender")
.PivotCache.MissingItemsLimit = xlMissingItemsNone
.PivotCache.Refresh
With .PivotFields("Name")
'---hide all items except item 1
.PivotItems(1).Visible = True
For i = 2 To .PivotItems.Count
.PivotItems(i).Visible = False
Next

With ActiveSheet.PivotTables("StreetAddress")
.PivotCache.MissingItemsLimit = xlMissingItemsNone
.PivotCache.Refresh
With .PivotFields("Name")
'---hide all items except item 1
.PivotItems(1).Visible = True
For i = 2 To .PivotItems.Count
.PivotItems(i).Visible = False
Next

For i = 1 To .PivotItems.Count
.PivotItems(i).Visible = True
If i <> 1 Then .PivotItems(i - 1).Visible = False
sItem = .PivotItems(i)

'this is followed by code to copy and paste the results of the pivots onto new sheets, which is working fine