+ Reply to Thread
Results 1 to 5 of 5

Manipulating a Column Label Filter on a Pivot using VBA

  1. #1
    Registered User
    Join Date
    06-29-2011
    Location
    Allen, TX
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    13

    Lightbulb Manipulating a Column Label Filter on a Pivot using VBA

    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
    Last edited by abhi.ko; 06-29-2011 at 08:18 PM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,876

    Re: Manipulating a Column Label Filter on a Pivot using VBA

    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.
    Attached Files Attached Files
    Last edited by MarvinP; 06-30-2011 at 12:01 AM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    07-07-2011
    Location
    Utah
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Manipulating a Column Label Filter on a Pivot using VBA

    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

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,876

    Re: Manipulating a Column Label Filter on a Pivot using VBA

    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.

  5. #5
    Registered User
    Join Date
    07-07-2011
    Location
    Utah
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Manipulating a Column Label Filter on a Pivot using VBA

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1