Pivot Table Report Style articls/links

[owentmoore]

Hi all

Does anyone know of any articles or websites I can look up to get a good understanding on how to create my own pivot table report style? i.e. Instead of selcting one of the 15 or so styles offered by Excel, I'd like to create my own and be able to apply it as required.

Thanks
Owen

 

[arthurbr]

Did you find anything on Google?

 

[owentmoore]

Yes. I've found several things.....

There's an application called AutoFormat XL for Pivot Tables that will save your modified pivot table as a template which you can then apply to other tables. It's a good program (you can download a trial version). Problem with this is that if the users change the table, the formatting will be lost. They would need to have the program to re-instate the formatting.

The code below is a really good example of pivot table style formatting that can be modified programatically.
I can't find the reference to the website I got it from. You'll need to create new styles with names "PivLabel", "PivData", "PivRow" and "PivColumn" and modify the styles to suit what you want (ie font, fill, borders etc)

Sub ApplyStyles()

Application.ScreenUpdating = False
    
    With ActiveSheet.PivotTables(3)
        
        '\ style for cells above row area of table
        .TableRange1.Style = "PivLabel"
        
        '\ style for data area
        .DataBodyRange.Style = "PivData"
        
        '\ row area
        .RowRange.Style = "PivRow"
        
        '\ column headings
        .ColumnRange.Style = "PivColumn"
        
        '\ adjust row and cell heights to fit new style
        .TableRange1.EntireColumn.AutoFit
        
        '\ adjust row and cell heights to fit new style
        .TableRange1.EntireRow.AutoFit
    
    End With
    
Application.ScreenUpdating = True

End Sub

Owen