Another Pivot Chart Autoformating Question

[SoBe]

I have read through the other posts; but I still need some additional help
since I do not have Macro or VBA experience.

I am looking for a easy solution to reset all the pivot charts to my
formatting as noted below (from a recorded Macro that didn't work) after a
data refresh. There are about 20 tabs in the workbook with a pivot table and
chart on each tab. A manual macro that works on all tabs or an auto run macro
that works after a refresh is what I'm looking for. Any suggestions?

Thanks in advance,

Sub FormatPivotChart()
' FormatPivotChart Macro
'
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 9
.Pattern = xlSolid
End With
With ActiveChart.ChartGroups(1)
.Overlap = 100
.GapWidth = 30
.HasSeriesLines = False
.VaryByCategories = False
End With
ActiveChart.Axes(xlCategory).Select
With Selection.Border
.Weight = xlHairline
.LineStyle = xlAutomatic
End With
With Selection
.MajorTickMark = xlNone
.MinorTickMark = xlNone
.TickLabelPosition = xlNextToAxis
End With
With Selection.TickLabels
.Alignment = xlCenter
.Offset = 100
.ReadingOrder = xlContext
.Orientation = xlHorizontal
End With
ActiveChart.ChartArea.Select
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
Selection.Interior.ColorIndex = xlNone
ActiveChart.ChartArea.Select
ActiveChart.HasTitle = False
End Sub

 

[Debra Dalgleish]

You can loop through the worksheets and embedded charts. This example
will modify the first chart on each worksheet:

'======================
Sub FormatPivotChart()
Dim ws As Worksheet
Dim ch As ChartObject

For Each ws In ThisWorkbook.Worksheets
If ws.ChartObjects.Count Then
With ws.ChartObjects(1).Chart.SeriesCollection(1)
With .Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
.Shadow = False
.InvertIfNegative = False
With .Interior
.ColorIndex = 9
.Pattern = xlSolid
End With
End With
With ws.ChartObjects(1).Chart.ChartGroups(1)
.Overlap = 100
.GapWidth = 30
.HasSeriesLines = False
.VaryByCategories = False
End With
With ws.ChartObjects(1).Chart.Axes(xlCategory)
With .Border
.Weight = xlHairline
.LineStyle = xlAutomatic
End With
.MajorTickMark = xlNone
.MinorTickMark = xlNone
.TickLabelPosition = xlNextToAxis
End With
With ws.ChartObjects(1).Chart.Axes(xlCategory).TickLabels
.Alignment = xlCenter
.Offset = 100
.ReadingOrder = xlContext
.Orientation = xlHorizontal
End With
With ws.ChartObjects(1).Chart.PlotArea.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
ws.ChartObjects(1).Chart.PlotArea.Interior.ColorIndex = xlNone
ws.ChartObjects(1).Chart.HasTitle = False
End If
Next ws

End Sub
'=========================

 

[SoBe]

Wow! Great Debra thanks so much.

You can loop through the worksheets and embedded charts. This example
will modify the first chart on each worksheet:

'======================
Sub FormatPivotChart()
Dim ws As Worksheet
Dim ch As ChartObject

For Each ws In ThisWorkbook.Worksheets
If ws.ChartObjects.Count Then
With ws.ChartObjects(1).Chart.SeriesCollection(1)
With .Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
.Shadow = False
.InvertIfNegative = False
With .Interior
.ColorIndex = 9
.Pattern = xlSolid
End With
End With
With ws.ChartObjects(1).Chart.ChartGroups(1)
.Overlap = 100
.GapWidth = 30
.HasSeriesLines = False
.VaryByCategories = False
End With
With ws.ChartObjects(1).Chart.Axes(xlCategory)
With .Border
.Weight = xlHairline
.LineStyle = xlAutomatic
End With
.MajorTickMark = xlNone
.MinorTickMark = xlNone
.TickLabelPosition = xlNextToAxis
End With
With ws.ChartObjects(1).Chart.Axes(xlCategory).TickLabels
.Alignment = xlCenter
.Offset = 100
.ReadingOrder = xlContext
.Orientation = xlHorizontal
End With
With ws.ChartObjects(1).Chart.PlotArea.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
ws.ChartObjects(1).Chart.PlotArea.Interior.ColorIndex = xlNone
ws.ChartObjects(1).Chart.HasTitle = False
End If
Next ws

End Sub
'=========================