countryfan_nt
Well-known Member
- Joined
- May 19, 2004
- Messages
- 765
Greetings, Hope all is safe & Well!
Please help me fix the bug with the below VBA; it is meant to filter the PowerPivot based on the values in range C2:C20 of sheet "Look".
Getting the following bug: "run time error 1004 unable to get the pivotfields property of the pivottable class"
Bugged line is: Sheets("Proc IDs & Names").PivotTables("pt").PivotFields(fldName).VisibleItemsList = ArrVisiblelist
Your kind help is truly appreciated in advance!
Please help me fix the bug with the below VBA; it is meant to filter the PowerPivot based on the values in range C2:C20 of sheet "Look".
Getting the following bug: "run time error 1004 unable to get the pivotfields property of the pivottable class"
Bugged line is: Sheets("Proc IDs & Names").PivotTables("pt").PivotFields(fldName).VisibleItemsList = ArrVisiblelist
Your kind help is truly appreciated in advance!
VBA Code:
Option Explicit
Sub Test1()
Dim MyString As String
Dim ArrVisiblelist()
Dim fldName As String
fldName = "[Proc XLSM].[Procedure ID].[Procedure ID]"
Dim lr As Long, i As Long
lr = Sheets("Look").Range("C" & Rows.Count).End(xlUp).Row
ReDim ArrVisiblelist(1 To lr - 1)
For i = 2 To lr
ArrVisiblelist(i - 1) = "[Proc XLSM].[ProcedureID].&[" & Sheets("Look").Cells(i, 2).Value & "]"
Sheets("Proc IDs & Names").PivotTables("pt").PivotFields(fldName).VisibleItemsList = ArrVisiblelist
Next i
End Sub