Macro to format all pivotcharts in a workbook

[jeremy nickels]

You know how Pivotcharts lose their formatting when you refresh the pivot
table they are attached to? Well I set up a macro to re-format the charts,
but my code only formats the currently selected chart. How can I change my
code to update all the pivot charts in my workbook?

Here's the code I have right now:

ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ApplyDataLabels Type:=xlDataLabelsShowValue, _
AutoText:=True, LegendKey:=False
ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlTransparent
End With

 

[Gary Brown]

Jeremy,
Suggestion: After creating your Pivot Table, go back into 'Table
Options' and uncheck 'AutoFormat table'. This will stop Excel from
automatically reformatting the Pivot Table that you just took so much time to
format exactly as you want it.

Here's a macro to change the AutoFormat option in all your Pivot Tables in
the active workbook.
'/-------------------------------------/
Sub ChangeAutoFormatting()
Dim iSheets As Integer, x As Integer
Dim iPivot As Integer, _
strCurrentSheet As String

On Error GoTo Exit_ChgAutoFmt

'Count number of sheets in workbook
iSheets = ActiveWorkbook.Sheets.Count

'remember current sheet
strCurrentSheet = ActiveSheet.Name

If Windows.Count = 0 Then _
GoTo Exit_ChgAutoFmt

For x = 1 To iSheets

'go to a worksheet to change pivot tables
Sheets(x).Activate

'turn warning messages off
Application.DisplayAlerts = False

'change all pivot tables on
' this worksheet one at a time
For iPivot = 1 To _
ActiveSheet.PivotTables.Count
ActiveSheet.PivotTables(iPivot). _
HasAutoFormat = False
Next

'turn warning messages on
Application.DisplayAlerts = True

Next

'return to worksheet that
' you were originally at
Application.ActiveWorkbook. _
Sheets(strCurrentSheet).Activate

Exit_ChgAutoFmt:
Application.CommandBars("PivotTable"). _
Visible = False
Application.DisplayAlerts = True

End Sub
'/-------------------------------------/

HTH,
Gary Brown

 

[jeremy nickels]

Gary,

It's not the pivot tables that I have a problem with, it's the pivot charts
that are linked to those pivot tables. Even when I remove "AutoFormat table"
on the pivot tables, the pivot charts linked to those pivot tables lose their
formatting when I refresh the data.

I read something in Excel help that said that it is not possible to retain
formatting on a pivot chart when the pivot table that it is linked to is
refreshed. It suggested writing a macro to re-format the charts after you
refresh. I wrote the macro, but it only re-formats one chart at a time. I
want it to re-format all the charts in a workbook, if possible.

 

[Gary Brown]

Sorry I misunderstood. Try this...
'/-------------------------------------/
Sub ChangePivotFormatting()
Dim cChart As Chart
Dim strCurrentSheet As String

On Error GoTo err_ChgPivotFmt

'remember current sheet
strCurrentSheet = ActiveSheet.Name

If Windows.Count = 0 Then _
GoTo Exit_ChgPivotFmt

For Each cChart In Charts
cChart.Activate
cChart.SeriesCollection(1).ApplyDataLabels _
Type:=xlDataLabelsShowValue, _
AutoText:=True, LegendKey:=False
cChart.SeriesCollection(1).DataLabels.Select
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlTransparent
End With
Next cChart
'return to worksheet that
' you were originally at
Application.ActiveWorkbook. _
Sheets(strCurrentSheet).Activate

Exit_ChgPivotFmt:

Exit Sub

err_ChgPivotFmt:
Debug.Print ActiveSheet.Name & " - " & _
Err.Number & " - " & Err.Description
GoTo Exit_ChgPivotFmt

End Sub
'/-------------------------------------/


HTH,
Gary Brown

 

[jeremy nickels]

Gary, it doesn't seem to be working. Let me describe my workbook in a little
more detail. I have about 18 worksheets in this workbook. Each worksheet
has 3 charts on it. Does knowing that change the code at all?

 

[Gary Brown]

Yes.
I assumed (made an ASS out of U and ME) that you were talking about chart
worksheets.
I've included code for multiple chart objects on various worksheets within a
workbook.

Try this (knock on wood - NO! Not my head :O>!)

'/-------------------------------------/
Sub ChangePivotFormatting()
Dim cChart As Chart
Dim coChart As ChartObject
Dim sht As Worksheet
Dim strCurrentSheet As String

On Error GoTo err_ChgPivotFmt

'remember current sheet
strCurrentSheet = ActiveSheet.Name

If Windows.Count = 0 Then _
GoTo Exit_ChgPivotFmt

For Each cChart In Charts
cChart.Activate
cChart.SeriesCollection(1).ApplyDataLabels _
Type:=xlDataLabelsShowValue, _
AutoText:=True, LegendKey:=False
cChart.SeriesCollection(1).DataLabels.Select
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlTransparent
End With
Next cChart

For Each sht In Worksheets
For Each coChart In sht.ChartObjects
coChart.Activate
ActiveChart.SeriesCollection(1).ApplyDataLabels _
Type:=xlDataLabelsShowValue, _
AutoText:=True, LegendKey:=False
ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlTransparent
End With
Next coChart
Next sht

'return to worksheet that
' you were originally at
Application.ActiveWorkbook. _
Sheets(strCurrentSheet).Activate

Exit_ChgPivotFmt:

Exit Sub

err_ChgPivotFmt:
Debug.Print ActiveSheet.Name & " - " & _
Err.Number & " - " & Err.Description
GoTo Exit_ChgPivotFmt

End Sub
'/-------------------------------------/

 

[jeremy nickels]

That did the trick Gary. You are a genius!

Yes.
I assumed (made an ASS out of U and ME) that you were talking about chart
worksheets.
I've included code for multiple chart objects on various worksheets within a
workbook.

Try this (knock on wood - NO! Not my head :O>!)

'/-------------------------------------/
Sub ChangePivotFormatting()
Dim cChart As Chart
Dim coChart As ChartObject
Dim sht As Worksheet
Dim strCurrentSheet As String

On Error GoTo err_ChgPivotFmt

'remember current sheet
strCurrentSheet = ActiveSheet.Name

If Windows.Count = 0 Then _
GoTo Exit_ChgPivotFmt

For Each cChart In Charts
cChart.Activate
cChart.SeriesCollection(1).ApplyDataLabels _
Type:=xlDataLabelsShowValue, _
AutoText:=True, LegendKey:=False
cChart.SeriesCollection(1).DataLabels.Select
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlTransparent
End With
Next cChart

For Each sht In Worksheets
For Each coChart In sht.ChartObjects
coChart.Activate
ActiveChart.SeriesCollection(1).ApplyDataLabels _
Type:=xlDataLabelsShowValue, _
AutoText:=True, LegendKey:=False
ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlTransparent
End With
Next coChart
Next sht

'return to worksheet that
' you were originally at
Application.ActiveWorkbook. _
Sheets(strCurrentSheet).Activate

Exit_ChgPivotFmt:

Exit Sub

err_ChgPivotFmt:
Debug.Print ActiveSheet.Name & " - " & _
Err.Number & " - " & Err.Description
GoTo Exit_ChgPivotFmt

End Sub
'/-------------------------------------/