Manipulating a Column Label Filter on a Pivot using VBA

**abhi.ko** · 06-29-2011, 08:15 PM

Hi,

I'm new to VBA and I'm trying to write a simple procedure to alter the filter selections on a Pivot table using VBA.

I am successful in manipulating the page fields (Pivot Filters) on the pivot using the following code, but have no idea on how to manipulate the column or row label field filters on the same table.

Here is the code I have written so far and the Bold statement is the one I'm having trouble with:

Please Login or Register  to view this content.

Here is a snapshot of the pivot table:

OM (All)
Line Director (All)

Count of Issue Site = Column Label / Filter
Month Albuquerque Boise Chennai Florence Gray Greensboro
Jan-11
Feb-11
Mar-11
Apr-11
May-11

The error line is in Bold Typeface, the rest of the code works well!
Any help on how I can update the column filter based on a value that is on another cell/sheet just like the page fields is what I'm looking for, and would be greatly appreciated!

Thanks,
Abhi

**MarvinP** · 06-29-2011, 11:57 PM

Hi abhi.ko and welcome to the forum,

You didn't start your first question here with a simple one. In my learning about VBA and Pivot Tables, I recorded a lot of Macros and looked at what they were doing as I changed filters and sorts on my Pivot Tables.

The best I can do is to give you an example I wrote a few months ago. This makes all 3 pivot tables on a sheet agree if your change one of their filters. I hope this gives you an idea of Index numbers in Pivots and can be used, somehow for your learning.

I've found another file with code to manipulate Pivot Tables. It might help also. You wil need to hunt to find the code for the second file.

**Jallred** · 07-08-2011, 11:45 AM

Marvin,

I am experiencing a similar problem as abhi.ko. I tried to use your code from the first attached workbook PivotTablesLinked.xlsm, and adjust it to my workbook. I'm fairly new to VBA and from what I'm able to tell I would need to change "Transaction Week" to the Pivot field that exists in my workbook after having copied and pasted the code to the Sheet that I am using. However, when i tried that, nothing happened. And now when i try to change the transaction week filter in your example workbook it doesn't work anymore either.

How do you trigger the macro and is there anything else in your example code that i would need to change?

Best,
Jallred

**MarvinP** · 07-08-2011, 07:33 PM

Hi,

Macros are triggered from Events (that are behind the worksheets themselves or the workbook). You can also create a button or shape to click on to run a macro.

You should really start your own thread with your problem and attach a sample workbook so we can see what the problem is all about.

**Jallred** · 07-11-2011, 06:04 PM

Marvin,

Thanks for getting back. I was able to manipulate things and get it to work. Next time I'll take your advice and begin my own thread.

Thanks again,

Jallred