Filter PowerPivot with range values.

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!

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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Forum statistics

Threads
1,223,248
Messages
6,171,027
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top