Filter Pivot Table based on cell value

Question

Hi there!

I need to filter a date field in pivot table in a dynamic way. From what I've searched, since I'm using an OLAP source I do not have many possibilities other than VBA.

I've created a cell within the worksheet where I'll retrieve the date needed: =WORKDAY(TODAY();2)

Then I tried recording a Macro of copying that value and pasting in the filter but obviously the macro got a fixed date and not the actual action of me copying and pasting.

Sub PTdatefilter()

'

' PTdatefilter Macro

'

'

    Range("C4").Select

    Selection.Copy

    ActiveSheet.PivotTables("PivotTable2").PivotFields( _

        "[Table].[date].[date]").VisibleItemsList = Array( _

        "[Table].[date].&[2016-10-13T00:00:00]")

    Range("C5").Select

    Application.CutCopyMode = False

    Selection.Copy

    ActiveSheet.PivotTables("PivotTable2").PivotFields( _

        "[Table].[date].[date]").VisibleItemsList = Array( _

        "[Table].[date].&[2016-10-12T00:00:00]", _

        "[Table].[date].&[2016-10-13T00:00:00]")

End Sub

This is what I currently have. I needed the dates to either be based on cells I created to have the dates I need, or just have it directly in the code. I saw that there's a application.workday function but I don't have enough VBA knowledge to use it :| ( yet )

Looking forward to your help!

Many thanks

Answer 1

We suggest you post your concern here as more Excel experts can help you with your concern/request.

Regards.

Answer 2

Hi Radney! 

Windows 7 desktop excel 2013. Thank you so much for assisting me!

Answer 3

Hi,

For us to assist you further, we need the following information:

• Which version of Excel are you using? (Excel 2016, Excel Online, etc...)
• What device are you using?

Regards.