EXCEL Macro Variable substitution in pivot filter setting command

[IanGW]

Hi, I am attempting to set a filter on an Excel pivot table using a variable in a macro.

When I execute this macro it works ok:

Sheets("Amort").Select
ActiveSheet.PivotTables("PivotTable7").PivotFields( _
"[Receiver].[Rcv 09 Name].[Rcv 09 Name]").ClearAllFilters
ActiveSheet.PivotTables("PivotTable7").PivotFields( _
"[Receiver].[Rcv 09 Name].[Rcv 09 Name]").CurrentPageName = _
"[Receiver].[Rcv 09 Name].&[BUSINESS X]"

The 'Rcv 09 Name' filter is set to BUSINESS X

I have another 8 of these command 'sets' in the macro, so one for each of the 9 sheets I need to update.

To save time I'd like to be able to set a BUSINESS name in a variable at the top of the macro and have that substitute into the last field in each set.... i.e. to set the BUSINESS filter

Something like this:

Dim u9name As String
u9name = "BUSINESS X"

Sheets("Amort").Select
ActiveSheet.PivotTables("PivotTable7").PivotFields( _
"[Receiver].[Rcv 09 Name].[Rcv 09 Name]").ClearAllFilters
ActiveSheet.PivotTables("PivotTable7").PivotFields( _
"[Receiver].[Rcv 09 Name].[Rcv 09 Name]").CurrentPageName = _
"[Receiver].[Rcv 09 Name].&[u9name]"

What would be the format/method for including the u9name variable in this command?

I have tried a few versions, and checked other threads, but I as yet no luck getting the substitution to work. The debig highlights the second ActivSheet command . I am new to macros so I hope this makes sense.

Using Excel 2007.
Thanks

 

[pbornemeier]

Welcome to the Board!

Try this

    Dim u9name As String
    u9name = "BUSINESS X"
    
    Sheets("Amort").Select
    ActiveSheet.PivotTables("PivotTable7").PivotFields( _
    "[Receiver].[Rcv 09 Name].[Rcv 09 Name]").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable7").PivotFields( _
    "[Receiver].[Rcv 09 Name].[Rcv 09 Name]").CurrentPageName = _
    "[Receiver].[Rcv 09 Name].[" & u9name & "]"

 

[IanGW]

That's it... brilliant. Many thanks for your help. I have adapted this to change 5 pivot table fields in 10 Worksheets within 20 Spreadsheets that I need to use to set up some intricate business level financial reporting... saving myself a few hours of filter setting. Which is great... or would be if not for the fact that I just got retrenched. (a few days after I posted the query). Such is life. At least I have enhanced my Macro skills. Thanks again.