将项目列表设置为多个枢轴表

Question

一直试图根据我在工作簿中的多个枢轴表中的一个中所做的更改，将相同的项列表设置为多个OLAP枢轴表。

记录的宏是(这也删除了旧的选择)：

Sub GetPivotFilter()

ActiveSheet.PivotTables("Pivottabell4").PivotFields( _
    "[DimCampaign].[Campaigns].[Campaign]").VisibleItemsList = Array( _
    "[DimCampaign].[Campaigns].[Campaign].&[106679]", _ 'generated after i select campaing
    "[DimCampaign].[Campaigns].[Campaign].&[106680]", _'generated after i select campaing

    "[DimCampaign].[Campaigns].[Campaign].&[107049]")'generated after i select campaing


End Sub

如何提取我在第一个枢轴中所做的更改(在进行更改之前，我不知道数字)？

2我怎样才能使它适用于那些可以选择加入竞选的枢轴表？

我目前的解决方案看起来很混乱，但效果很好：

Sub SetPivotFilter()

ActiveSheet.PivotTables("Pivottabell4").PivotFields( _
"[DimCampaign].[Campaigns].[Campaign]").VisibleItemsList = Array( _
"[DimCampaign].[Campaigns].[Campaign].&[106679]", _     
"[DimCampaign].[Campaigns].[Campaign].&[106680]", _    
"[DimCampaign].[Campaigns].[Campaign].&[107049]")
ActiveSheet.PivotTables("Pivottabell18").PivotFields( _
"[DimCampaign].[Campaigns].[Campaign]").VisibleItemsList = Array( _
"[DimCampaign].[Campaigns].[Campaign].&[106679]", _     
"[DimCampaign].[Campaigns].[Campaign].&[106680]", _    
"[DimCampaign].[Campaigns].[Campaign].&[107049]")
'and so on
End Sub 

我们非常感谢所有的帮助。

Answer 1

下面循环遍历所有PivotTable名称的代码是pvtTblNameArr (数组)，在工作表名"SheetPivot“中(根据工作表名称修改)。

Option Explicit

Sub GetPivotFilter()

Dim PvtTbl              As PivotTable
Dim pvtitem             As PivotItem
Dim pvtFld              As PivotField
Dim pvtTblNameArr()     As Variant
Dim i                   As Integer

' modify this array to all PivotTable names you need to modify (in the same sheet)
pvtTblNameArr() = Array("Pivottabell4", "Pivottabell8")

' loop though Pivot Table Names
For i = LBound(pvtTblNameArr) To UBound(pvtTblNameArr)

    ' change "SheetPivot" to your sheet name
    Set PvtTbl = ThisWorkbook.Sheets("SheetPivot").PivotTables(pvtTblNameArr(i))

    ' set Pivot field variable to "Campaign"
    Set pvtFld = PvtTbl.PivotFields("Campaign")

    ' lop through all Pivot Items in "Campaign" Pivot Field
    For Each pvtitem In pvtFld.PivotItems

        Select Case pvtitem.Name
            Case 106679, 106680, 107049
                pvtitem.Visible = True

            Case Else
                pvtitem.Visible = False

        End Select

    Next pvtitem

Next i

End Sub