Filter pivot table using VBA

[gsimons85]

Hello everyone

I got some help on this site last week on this issue and it worked at the time, but now my macro keeps crashing.

I'm trying to filter a pivot table using a cell reference (cell CL1) but I keep getting an error message. The code I have is as follows:

Dim Week As String
Week = Range("cl1").Value


'Dim PvtTbl As PivotTable
'Set PvtTbl = Worksheets("Pivot").PivotTables("PivotTable2")

ActiveSheet.PivotTables("PivotTable2").PivotFields("Week").CurrentPage = Week


PvtTbl.PivotFields("Week").PivotFilters.Add Type:=xlCaptionContains, Value1:=Week


If someone could be so kind as to point me in the right direction, that'd be much appreciated.

Thanks!
Greg

 

[Andrew Poulsom]

What's in CL1 when you get the error? What error are you getting an on what line of code? I notice that you have commented out the line that assigns your pivot table to the variable PvtTbl.