Forum Discussion
VBA method to iterate through all items in Power Pivot filter
Hello Community,
Does anyone know what is the right VBA method to iterate through all items in the Power Pivot table filter?
I need to create a separate copy of the master power pivot table on a separate sheet for each value of the power pivot table filter; it seems like traditional methods like 'PivotItems' don't work with power pivot.
Thank you!
2 Replies
- Sub CopyPivotTableByFilter()
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim filterField As String
Dim newWs As Worksheet
Dim i As Integer
' Specify the name of the worksheet containing the Power Pivot table
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name
Set pt = ws.PivotTables("PivotTable1") ' Change to your PivotTable name
' Specify the field that is used as a filter
filterField = "FieldName" ' Change to your filter field name
' Reference the filter field
Set pf = pt.PivotFields(filterField)
' Loop through each item in the filter
For Each pi In pf.PivotItems
' Create a new worksheet for each filter item
Set newWs = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
newWs.Name = pi.Name
' Copy the PivotTable to the new worksheet
pt.TableRange2.Copy
newWs.Range("A1").PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
' Apply the filter to the PivotTable in the new worksheet
With newWs.PivotTables(1).PivotFields(filterField)
.ClearAllFilters
.CurrentPage = pi.Name
End With
' Optional: Adjust formatting or additional settings as needed
Next pi
End SubAshaKantaSharma Thanks, but this would only work with a traditional Pivot Table. Power Pivots require a different approach- they are OLAP cube sourced and require 'CubeField' VBA methods
