I think it's something simple, but I'm not that smart in VBA terms

[gsimons85]

Hello again

This is my second post trying to resolve this issue, hopefully I explain it better this time and can get some help.

I'm trying to filter on certain criteria for my pivot table and want to reference a particular set of cells.

Here is the code I got when I recorded my macro and manually filter:

Range("C1").Select
ActiveSheet.Range("$C$1:$D$28").AutoFilter Field:=1, Criteria1:=Array("1", _
"3", "YTD", "Z56"), Operator:=xlFilterValues


Instead of referencing "1" or "3", I'd like to reference the value in Cell A2 and A3 so that I can change the cell value and the pivot picks up the new value.

[TABLE="width: 500"]
<TBODY>[TR]
[TD]Ref Cells[/TD]
[TD]Week[/TD]
[TD]Dollars[/TD]
[TD]State[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]10[/TD]
[TD]Tx[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]10[/TD]
[TD]Tx[/TD]
[/TR]
[TR]
[TD]Z56[/TD]
[TD]2[/TD]
[TD]15[/TD]
[TD]Tx[/TD]
[/TR]
[TR]
[TD]YTD[/TD]
[TD]2[/TD]
[TD]15[/TD]
[TD]Tx[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]20[/TD]
[TD]Tx[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]20[/TD]
[TD]Ca[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4[/TD]
[TD]25[/TD]
[TD]Ca[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4[/TD]
[TD]25[/TD]
[TD]Ca[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]YTD[/TD]
[TD]30[/TD]
[TD]Ca[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]YTD[/TD]
[TD]30[/TD]
[TD]Co[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Z56[/TD]
[TD]100[/TD]
[TD]Co[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Z56[/TD]
[TD]100[/TD]
[TD]Co[/TD]
[/TR]
</TBODY>[/TABLE]


I'll keep working on it, but if someone could help point me in the right direction I'd be a very happy camper.

Thank you
Greg

 

[JackDanIce]

Not sure if this will work but try:

Range("C1").Select
ActiveSheet.Range("$C$1:$D$28").AutoFilter Field:=1, Criteria1:=Array(Range("A1").Value, _
Range("A2").Value, Range("A3").Value, Range("A4").Value), Operator:=xlFilterValues

 

[gsimons85]

Not sure if this will work but try:

Range("C1").Select
ActiveSheet.Range("$C$1:$D$28").AutoFilter Field:=1, Criteria1:=Array(Range("A1").Value, _
Range("A2").Value, Range("A3").Value, Range("A4").Value), Operator:=xlFilterValues

When I enter this code, only Z56 and YTD get filtered, but the first two numbers are not included...

 

[JackDanIce]

Made a minor change and this now works for me, change in red:

Range("C1").Select   '<--- you can delete this line, it's doing nothing
ActiveSheet.Range("$C$1:$D$28").Autofilter Field:=1, Criteria1:=Array(CST(Range("A2").Value), _
  CST(Range("A3").Value), Range("A4").Value, Range("A5").Value), Operator:=xlFilterValues

 

[gsimons85]

Made a minor change and this now works for me, change in red:

Range("C1").Select   '<--- you can delete this line, it's doing nothing
ActiveSheet.Range("$C$1:$D$28").Autofilter Field:=1, Criteria1:=Array(CST(Range("A2").Value), _
  CST(Range("A3").Value), Range("A4").Value, Range("A5").Value), Operator:=xlFilterValues

ActiveSheet.Range("$C$1:$D$28").AutoFilter Field:=1, Criteria1:=Array(CST(Range("A2").Value), _
CST(Range("A3").Value), Range("A4").Value, Range("A5").Value), Operator:=xlFilterValues

When I enter this code, I get a "COMPILE ERROR:SUB OR FUNCTION NOT DEFINED"

I figured out that when I change the numbers to Text format in the cells, it picks up with the original formula you provided...but I'd like to be able to pick up those cell values without converting to Text if possible....

Thanks for your help!

 

[gsimons85]

I think I see what I was missing...I used CSTr instead of CST and it worked!!

ActiveSheet.Range("$C$1:$D$28").AutoFilter Field:=1, Criteria1:=Array(CStr(Range("A2").Value), _
CStr(Range("A3").Value), Range("A4").Value, Range("A5").Value), Operator:=xlFilterValues

Thank you very much for your help and assistance!

 

[JackDanIce]

You're welcome, glad it works