VBA - how to use a cell value to filter a PivotTable with a macro

[landjuweeltje]

Hello MrExcel,

I made a macro to perform certain filtering actions on a PivotTable, but now I want to use a cell value to make the macro filter on a specific month, so that I don't have to adapt the macro each month.

I thought this would be very simple, but I've tried several ways and I got stuck in each of them, maybe I'm not using the right syntax or maybe I forgot something? (or did I make some stupid mistakes...?) Or do you have easier or better solutions that I could try?

So if there's "05" in the cell, I want the macro to filter the PivotTable for the month of May (the PivotItem "Month" already contains the months as "01", "02" etc).

Till now I tried this:

ActiveSheet.PivotTables("PivotTable4").PivotFields("Month").CurrentPage.Range
("E1").Value

Where cell E1 contained the value that I wanted to use. I got the error "Run-time error '438': Object doesn't support this property or method".

And several variations of this:

Sub Macro1()
Dim FTW As String
Call SetVarFromCell
'FTW = SetVarFromCell()
ActiveSheet.PivotTables("PivotTable4").PivotFields("Month").CurrentPage = FTW
End Sub
 
Sub SetVarFromCell()
Dim FTW As String
Windows("Example.xls").Activate
FTW = Worksheets("SheetOne").Cells(15, "D").Value
End Sub

In this case cell D15 in SheetOne contains the value that I want to use.
But then I got the error "Run-time error '1004': Unable to set the _Default property of the PivotItem class". Clicking Debug shows that the script stopped at the line with ActiveSheet.PivotTables....

I've been searching the forums for weeks already trying to find an answer, so I thought it was time to finally post a question myself...

I'm using Excel 2003 on Win XP.

I would really appreciate some hints to get me on track.

Thanks in advance!

Kind regards,
Landjuweeltje

 

[landjuweeltje]

Was I on the right track with my attempts, or should I try something completely different?