PDA

View Full Version : [SOLVED] Need help with Multiple Pivot Table PivotField filters



Poundland
04-13-2017, 07:01 AM
All,

I am attempting to Filter a PivotField of a Pivotable for 600 independent values so the Pivot Table displays just the values for the 600 fields.

I have written the below code, and it works by filtering the PivotField, but it only ever displays 1 Pivotfield and does not add to the filter to show all 600.

I probably need to build an array, but not sure how to build a 600 multiple string array?

Or perhaps I need to add some additional code to the PivotTable code to add to the filter each time rather than just change it?


Sub Filter_Pivot()

Dim D(1 To 600) As String, SD(1 To 600) As String, C(1 To 600) As String, SC(1 To 600) As String, M(1 To 600) As String
Dim Arr(1 To 600) As String
Dim wrkThis As Workbook, ShtSls As Worksheet, shtTop As Worksheet


Set wrkThis = ThisWorkbook
Set ShtSls = wrkThis.Sheets("Sales Cubes")
Set shtTop = wrkThis.Sheets("Top 600")

For A = 1 To 600

D(A) = shtTop.Cells(1 + A, 68).Value
SD(A) = shtTop.Cells(1 + A, 70).Value
C(A) = shtTop.Cells(1 + A, 72).Value
SC(A) = shtTop.Cells(1 + A, 74).Value
M(A) = shtTop.Cells(1 + A, 76).Value
Arr(A) = "[Product].[Product Range Master Article].[Master Article].&[" & D(A) & "]&[" & SD(A) & "]&[" & C(A) & "]&[" & SC(A) & "]&[" & M(A) & "]"
Debug.Print (Arr(A))

' [Product].[Product Range Master Article].[Master Article].&[850]&[862]&[107]&[101]&[IM03936] EXAMPLE
ShtSls.PivotTables("PivotTable1").PivotFields( _
"[Product].[Product Range Master Article].[Master Article]").VisibleItemsList _
= Array(Arr(A))

Next A


Exit Sub

Poundland
04-19-2017, 03:53 AM
Below is my code I have updated to solve this issue; changes have been commented


Sub Filter_Pivot()

Dim D(1 To 600) As String, SD(1 To 600) As String, C(1 To 600) As String, SC(1 To 600) As String, M(1 To 600) As String
Dim Arr(1 To 600) As String, MyArray(600) As String ' Array Declaration and Size defined
Dim wrkThis As Workbook, ShtSls As Worksheet, shtTop As Worksheet


Set wrkThis = ThisWorkbook
Set ShtSls = wrkThis.Sheets("Sales Cubes")
Set shtTop = wrkThis.Sheets("Top 600")

'ReDim MyArray(600)
For A = 1 To 600

D(A) = shtTop.Cells(1 + A, 68).Value
SD(A) = shtTop.Cells(1 + A, 70).Value
C(A) = shtTop.Cells(1 + A, 72).Value
SC(A) = shtTop.Cells(1 + A, 74).Value
M(A) = shtTop.Cells(1 + A, 76).Value
Arr(A) = "[Product].[Product Range Master Article].[Master Article].&[" & D(A) & "]&[" & SD(A) & "]&[" & C(A) & "]&[" & SC(A) & "]&[" & M(A) & "]"
MyArray(A - 1) = Arr(A) ' Code Line adds each of the 600 elements to the Array from 0 to 599 = 600 elements
' [Product].[Product Range Master Article].[Master Article].&[850]&[862]&[107]&[101]&[IM03936] EXAMPLE
Next A

ShtSls.PivotTables("PivotTable1").PivotFields( _
"[Product].[Product Range Master Article].[Master Article]").VisibleItemsList _
=Array(MyArray) ' MyArray elements are used to filter the Pivot Table

Exit Sub