Filtering Pivot table using Cell Reference

[gsimons85]

Hello

I'm trying to filter a pivot table using a cell reference as my "value", which is current week #. For some reason, I keep getting autofilter 1004 error code and cant seem to figure out why.

My current code is as follows:
Sheets("Pivot").Select
ActiveSheet.Range("$A$1:$AV$950000").AutoFilter Field:=8, Criteria1:=Array(Format(Range("cl1").Value, "00")), Operator:=xlFilterValues

Part of the problem may be that columns A:AN are all filter fields, while columns AO:AV are the values...so i dont know, I tried A:AN in my ActiveSheet Range, I tried A2 instead of A1...i'm stuck :-/

If someone can help me filter this pivot based on cell reference, that'd be most excellent.

Thanks in advance!
Greg

 

[Andrew Poulsom]

Why Array?

 

[gsimons85]

This is just the code I got from here a while back when using a cell reference to filter a set of data and it's always worked...Sorry, not VBA smart enough to give you a better answer

 

[Andrew Poulsom]

That code is AutoFiltering a Range not Filtering a Pivot Table. You will get some VBA code if you record a macro while filtering the Pivot Table manually.

 

[gsimons85]

I recorded macro filtering on the week I need and got the following code:

Windows("2014 Conversion Pivot for reporting.xlsx").Activate
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Week")
.PivotItems("1").Visible = False
.PivotItems("2").Visible = False
.PivotItems("3").Visible = False
.PivotItems("4").Visible = False
.PivotItems("5").Visible = False
.PivotItems("6").Visible = False
.PivotItems("7").Visible = False
.PivotItems("8").Visible = False
.PivotItems("9").Visible = False
.PivotItems("10").Visible = False
.PivotItems("11").Visible = False
.PivotItems("12").Visible = False
.PivotItems("13").Visible = False
.PivotItems("14").Visible = False
.PivotItems("15").Visible = False
.PivotItems("16").Visible = False
.PivotItems("17").Visible = False
.PivotItems("18").Visible = False
.PivotItems("19").Visible = False
.PivotItems("20").Visible = False
.PivotItems("21").Visible = False
.PivotItems("22").Visible = False
.PivotItems("23").Visible = False
.PivotItems("24").Visible = False
.PivotItems("25").Visible = False
.PivotItems("26").Visible = False
.PivotItems("27").Visible = False
.PivotItems("28").Visible = False
.PivotItems("29").Visible = False
.PivotItems("30").Visible = False
.PivotItems("31").Visible = False
.PivotItems("32").Visible = False
.PivotItems("(blank)").Visible = False
.PivotItems("33").Visible = False
.PivotItems("35").Visible = False
End With




this code seems very long...can I not do something like the following:

Sub PivotTableFilter5()

Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")

PvtTbl.ClearAllFilters

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

End Sub

Instead of Value1 referencing "LO", cant I just edit to make it reference a cell value?

 

[Andrew Poulsom]

Did you try it?

 

[gsimons85]

Yes, but I cant figure out how to change from "LO" to reference cell value...I found some of your old posts and trying to piece together, but no luck yet...would you know how to properly change that last piece?

 

[Andrew Poulsom]

Is it?

PvtTbl.PivotFields("Week").PivotFilters.Add Type:=xlCaptionContains, Value1:=Format(Range("cl1").Value, "00")

 

[gsimons85]

I ultimately ended up with the following code which seems to be working:

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


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


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

Thanks Andrew for you assistance.!