VBA Pivot Table filter on dynamic range of cell values

MartinvH

New Member
Joined
Sep 26, 2013
Messages
2
Hey all,

I have been searching for a while, but I still have not found a working solution for my problem. Basically I have a pivot table over a huge data set consisting of all GL bookings from the past years. Now I want to filter the pivot table based on the date the GL bookings are posted.

The dates that I currently have in the pivot table are in cell C2, C3 and C4. When running the below code, the pivot table does filter on the dates in these three cells. However, it can be that I want to filter on -for example- 10 dates in cell C2 untill C11. For now, I manually have to adjust the code so it will contain every date filled in. But my question is, how can I make the coe dynamic so that the pivot table will be filtered on all values in C2 untill C..?

VBA Code:
    ActiveSheet.PivotTables("PivotTable2").PivotFields( _
        "[Finall Datum].[Hierarchy].[Datum]").VisibleItemsList = Array( _
        "[Finall Datum].[Hierarchy].[Datum].&" & Range("C2").Value, _
        "[Finall Datum].[Hierarchy].[Datum].&" & Range("C3").Value, _
        "[Finall Datum].[Hierarchy].[Datum].&" & Range("C4").Value)

Many thanks for the help, support and -hopefully- an answer.

With kind regards,
Martin
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Forum statistics

Threads
1,141,772
Messages
5,708,447
Members
421,570
Latest member
BaileyJ

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
Top