auto-formatting pivot tables

[miconian]

I have a large workbook, with similar pivot tables on every sheet. Some rows of the pivot tables have fill color. I need to loop through the workbook and make these fill colors consistent.

I set about doing this by recording a macro of myself putting in the fill color for one particular type of row, and here's what it produced:

PivotFormat()
'
'PivotFormat
'
    ActiveSheet.PivotTables("PivotTable12").PivotSelect _
        "'Sold To Customer'[All;Total] Jones", xlDataAndLabel, True
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
End Sub

Notice the word "Jones" on the second line of code. Jones is the data selected under "report filter." That can't be in the generic code, because with each worksheet, a different person's name is going to appear there.

Perhaps there is some kind of wildcard variable I can put into the code instead of "Jones"?

I'm also open to different approaches. All these pivot tables pull data from the same source, if that matters.

Thank you!

 

[miconian]

All I'm trying to do is change the fill color of certain rows in multiple pivot tables.

I recorded a macro of myself making the change manually in one particular pivot table. The plan is to create a loop to go through a bunch of other pivot tables and make the same change (fill color).

I'm trying to figure out how to make all the references truly relative, so that the macro will work on any pivot table, not just the one I recorded the macro with. Also, I don't want the code to make any changes to the pivot table besides the fill color.

The report filter actually has nothing to do with the problem I'm trying to solve... but the data from the report filter is showing up in the code. In this macro, that data is "Jones" because that's what the report filter was set to when the macro was recorded. I did not touch the report filter while I was recording the macro. The contents of the report filter field should not matter to the macro.

I only want this macro to do one thing: change the rows corresponding to "Sold To Customer' to .TintAndShade = 0.599993896298105. ("Sold To Customer" is a field being used as a Row Label in the report.)

Thanks!