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!

 

[texasalynn]

Are you using the name as a page field? I'm not sure where "report filter" is references to?

 

[miconian]

Based on a little searching, it does seem that Report Filter and Page Field are the same thing, perhaps from different versions of Excel. I'm in Excel 2007 for Windows.

 

[texasalynn]

it looks like the report filter and page filter are the differences of 2007/2003. That field is usually in the same cell so it would seem that you could have the macro read that field. I would setup the macro to assign that cell value as a variable. I do not have 2007 here so I can't test it out for you. But maybe that will give a direction to work towards

 

[miconian]

You're right, but I don't know how to assign that value as a variable...

 

[texasalynn]

to assign a value in a cell use something like:

pvreport = Range("a1")

ActiveSheet.PivotTables("PivotTable12").PivotSelect _
pvreport, xlDataAndLabel, True

this is untested

 

[miconian]

I'm not trying to assign a value to a cell, and I'm really not sure how your last comment relates to my question. But thanks for reading.

 

[texasalynn]

Ok my reply wasn't assign a value to a cell, but it is reading a value from a cell. That value is then used in the macro.

 

[miconian]

I'm not trying to read a value from a cell either.

 

[texasalynn]

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"?

ok let's go back. Your first post said you were looking for a variable to read the data of the report filter. Is that right? That was what I was answering? So now I'm confused