Using VBA to change Cube Linked Pivot Table based off of another Pivot Table (linking two pivot tables)
I have a workbook with several pivot tables that are build off of cubes (OLAP queries are run to refresh them). They have the same pivot fields, so I am looking to link them together. If I change the values that are in pivottable 1, I would like it to change the values for all of the other pivot tables in the workbook.

My initial thought was to use the worksheet_change event and when there is a change to note what changed and then apply it to all of the other pivot tables. Looking around it seems like I would have to loop through each pivot item and if it was visible then I would activate it in the other pivot tables.

    Sub Worksheet_Change(ByVal Target As Range)


    If Not Application.Intersect(Target, Sheets(1).Range("B38")) Is     Nothing Then

         Sheets(1).PivotTables("PivotTable1").PivotFields("[f1]").CubeField.EnableMultiplePageItems = True
        Dim counter As Integer
        counter = 0
        Dim pitem As PivotItem
        For Each pitem In Sheets(1).PivotTable("PivotTable1")
        If pitem.Visible = True Then
        If counter = 0 Then
        
        Sheets(2).PivotTables("PivotTable1").PivotFields("[f1]").AddPageItem "[f1].[Division Id].&[" & pitem & "]", True
        
        Else
        Sheets(2).PivotTables("PivotTable1").PivotFields("[f1]").AddPageItem "[Sales Org with Clinic].[Division Id].&[" & pitem & "]"
        End If
        
        End If
         
        Next pitem
    End If
 End Sub

When I was looking at the macro recorder I was interested to find that pivot items are not what you seem to use here, but rather pageitems. I figured I would just swap it in, like I did above, but that runs into problems. For starters my for each loop is apparently operating on an object that does not support it. When I try to loop through the pageitems it says that it is an unknown type.

So I am looking for some thoughts on how best to go about it. I just want to make it so when I modify one pivot table, then several other pivot tables are modified. The cube component seems to be throwing a wrench in my approach. I am interested in any different takes on solutions, this is just what came to me first.

Thanks as always SO.
Using VBA to change Cube Linked Pivot Table based off of another Pivot Table (linking two pivot tables)
===

I have a workbook with several pivot tables that are build off of cubes (OLAP queries are run to refresh them). They have the same pivot fields, so I am looking to link them together. If I change the values that are in pivottable 1, I would like it to change the values for all of the other pivot tables in the workbook.

My initial thought was to use the worksheet_change event and when there is a change to note what changed and then apply it to all of the other pivot tables. Looking around it seems like I would have to loop through each pivot item and if it was visible then I would activate it in the other pivot tables.

    Sub Worksheet_Change(ByVal Target As Range)


    If Not Application.Intersect(Target, Sheets(1).Range("B38")) Is     Nothing Then

         Sheets(1).PivotTables("PivotTable1").PivotFields("[f1]").CubeField.EnableMultiplePageItems = True
        Dim counter As Integer
        counter = 0
        Dim pitem As PivotItem
        For Each pitem In Sheets(1).PivotTable("PivotTable1")
        If pitem.Visible = True Then
        If counter = 0 Then
        
        Sheets(2).PivotTables("PivotTable1").PivotFields("[f1]").AddPageItem "[f1].[Division Id].&[" & pitem & "]", True
        
        Else
        Sheets(2).PivotTables("PivotTable1").PivotFields("[f1]").AddPageItem "[Sales Org with Clinic].[Division Id].&[" & pitem & "]"
        End If
        
        End If
         
        Next pitem
    End If
 End Sub

When I was looking at the macro recorder I was interested to find that pivot items are not what you seem to use here, but rather pageitems. I figured I would just swap it in, like I did above, but that runs into problems. For starters my for each loop is apparently operating on an object that does not support it. When I try to loop through the pageitems it says that it is an unknown type.

So I am looking for some thoughts on how best to go about it. I just want to make it so when I modify one pivot table, then several other pivot tables are modified. The cube component seems to be throwing a wrench in my approach. I am interested in any different takes on solutions, this is just what came to me first.

Thanks as always SO.