Control a Pivot Table with VBA 2

[MattGreer]

I'm using Excel 2010. I want to adjust the Columns by using the contents of a given cell, perhaps just the active cell, so I can click on a cell, click a button, and have the Columns be filtered by the contents of the active cell.

So, for example, I have in the active cell the value "2100". I want to click a button and have the columns adjust the filter to "contains 2100".

I recorded a macro, and then tried to adjust the code but no workie:

aa = ActiveCell.Value

ActiveSheet.PivotTables("PivotTable1").PivotFields("Xmitter").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Xmitter").PivotFilters.Add _
Type:=xlCaptionContains, Value1:=aa

If I type in "2100" instead of "aa", it works. But the variable for whatever reason throws it off. What have I done wrong?

Thanks for your help!

Thanks!!


Matt

 

[Andrzejek]

What do you get in Immediate Window if you do this:

aa = ActiveCell.Value[blue]
Debug.Print "*" & aa & "*"[/blue]
...

Do you get:

[tt]*2100*[/tt]

Have fun.

---- Andy

 

[MattGreer]

Yes, I did.

Is the issue that the expression requires quotes around it? /trying that in the meantime

Thanks!!


Matt

 

[MattGreer]

The pivot table filter works if I put in:

aa = "2420"

but aa = activecell.value will not work.

I might just copy a bunch of numbers into an array into the VBA and then cycle through them or something. But it would be nice to be able to enter a number into a cell and click a button and have the chart update. Still would appreciate help with that task.


Thanks!!


Matt

 

[SkipVought]

Value1:= """" & aa & """"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

and if all the quotes are confusing, try:

Value1:= Chr(34) & ActiveCell.Value & Chr(34)

Have fun.

---- Andy

 

[MattGreer]

I did try that, and unfortunately it didn't work Skip. It puts (literally) "2420" into the filter box from the pivot table. So I tried several combinations and this worked: "" & aa & ""

Problem solved!

Thanks to both of you for replying!

Thanks!!


Matt