Excel 2007 Macro to Update Pivot Table Filters depending on a Cell Value

    General discussion

  • Hi,

    I am new to VBA macros and am in need of some help.

    I have an Excel 2007 Excel workbook with multiple sheets (about 10), each sheet has a Pivot table based on a different Analysis Cube but they all have a similar filter such as Curreny either Local or Sterling.

    I have an initial sheet and what I want is when I change a value in a cell it needs to update the filter on each of the pivot tables on the seperate sheets.

    I have done some research and am not sure if I am heading in the right direction using information supplied on this post but I just get errors.  (Unable to get PivotFields property of the PivotTable Class).  I think this post is talking about Excel 2003 and not 2007.

    Any help much appreciated.

    • Changed type Tim Li Friday, September 18, 2009 3:26 AM no follow up
    Wednesday, September 09, 2009 8:22 PM

All replies

  • Update:

    I have been doing some more investigation and recording of Macro's

     ActiveSheet.PivotTables("PivotTable2").PivotFields("[Currency].[Bal Currency].[Currency]").CurrentPageName = "[Currency].[Bal Currency].[Currency].&[0]

    I have the above working but what I am not sure is what I now need to do to replace the .&[0] with a value past from the cell on my initial sheet as I assume that the .&[0] refers to an catorgory code or MUN value of the Analysis service cube and I have a value such as Local.

    I hope that above makes sense.

    Wednesday, September 09, 2009 9:55 PM
  • Hello,

    I'm not sure if I hit the problem, so you want to combine two strings?
    what is the result you want?

    The result of  "[Currency].[Bal Currency].[Currency]"&"[0]" is "[Currency].[Bal Currency].[Currency][0]".


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Tuesday, September 15, 2009 9:08 AM

  • We are changing the issue type to “Comment” because you have not followed up with the necessary information. If you have more time to look at the issue and provide more information, please feel free to change the issue type back to “Question” by editing your initial post and changing the dropdown list at the top of the post editor window. If the issue is resolved, we will appreciate it if you can share the solution so that the answer can be found and used by other community members having similar questions.
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Friday, September 18, 2009 3:26 AM
  • Hi Every one,

    I need to write a macro  in such a way that when I press a command button on the sheet, the macro should  assign a Hyperlink to very value in the cell, and this Hyperlink should be of type Name, value pair  where the Values that I want to pass are the column header and the row header.

    How can I do this ?

    Your help is appreciated.


    Monday, October 05, 2009 12:48 PM