Sometimes you have a whole workbook full of charts. You made some last week, and others yesterday, but the one you made today has just the right look to it. You’d like to apply the formatting to the rest of the charts, but the thought of reformatting all of those charts makes your brain ache.
The good news is that you don’t need to reformat every little element of every last chart. When you copy a chart, you can select another chart and go to Paste Special. The options in the Paste Special dialog are to paste the formulas (the data in the copied chart), the formats (from the copied chart), or both data and formatting.
Not only can you paste formats from one chart to another, you can write a little VBA to automate the process.
Copy and Paste Chart Formatting in VBA
John Mansfield did just that in VBA to Copy Embedded Chart Formatting in his cellMatrix.net blog. I punched up John’s original code to get this procedure:
Sub Copy_Chart_Formats()
Dim Sht As Worksheet
Dim Cht As ChartObject
Application.ScreenUpdating = False
ActiveChart.ChartArea.Copy
For Each Sht In ActiveWorkbook.Worksheets
For Each Cht In Sht.ChartObjects
Cht.Chart.Paste Type:=xlFormats
Next Cht
Next Sht
Application.ScreenUpdating = True
End Sub
Let’s see how it works. Here are four charts, with different data and with different original chart types and formatting.
With the top left chart selected, running the program changes none of the data but all of the formatting of the charts (including the top left chart).
Closer inspection reveals the bad news, which is that not only are the formats changed to match the master chart, but so are the chart and axis titles. This would be horrible, to get the formats you wanted in all 200 charts in your workbook, but to also have 200 sets of titles to change back.
Fortunately we’re all expert programmers, with a thorough knowledge of Excel’s object model, so we recognize before even trashing all of our charts what steps we need to take.
Copy and Paste Chart Formatting in VBA but Keep Original Titles
First we’ll do a little check before reformatting a chart, to save us the fraction of a second it takes to redundantly reformat the original chart. Then we’ll extract the chart and axis titles, apply the master format to the chart (which hoses the titles), and finally reapply the titles. The new code is highlighted in blue.
Sub Copy_Chart_Formats_Not_Titles() Dim Sht As Worksheet Dim Cht As ChartObject Dim chtMaster As Chart Dim bTitle As Boolean Dim bXTitle As Boolean Dim bYTitle As Boolean Dim sTitle As String Dim sXTitle As String Dim sYTitle As String Application.ScreenUpdating = False Set chtMaster = ActiveChart For Each Sht In ActiveWorkbook.Worksheets Sht.Activate For Each Cht In Sht.ChartObjects If Sht.Name = chtMaster.Parent.Parent.Name And _ Cht.Name = chtMaster.Parent.Name Then ' don't waste time on chtMaster Else With Cht.Chart ' get titles bTitle = .HasTitle If bTitle Then ' chart title exists sTitle = .ChartTitle.Characters.Text End If If .HasAxis(xlCategory) Then bXTitle = .Axes(xlCategory).HasTitle If bXTitle Then ' axis title exists sXTitle = .Axes(xlCategory).AxisTitle.Characters.Text End If End If If .HasAxis(xlValue) Then bYTitle = .Axes(xlValue).HasTitle If bYTitle Then ' axis title exists sYTitle = .Axes(xlValue).AxisTitle.Characters.Text End If End If ' copy formats chtMaster.ChartArea.Copy ' apply formats ' NOTE: this doesn't work properly in Excel 2007 onwards ' it behaves like xlPasteAll '.Paste Type:=xlFormats ' use this unintuitive approach instead .ChartArea.Select ActiveSheet.PasteSpecial Format:=2 ' restore titles If bTitle Then .HasTitle = True .ChartTitle.Characters.Text = sTitle Else .HasTitle = False End If If bXTitle Then .Axes(xlCategory).HasTitle = True .Axes(xlCategory).AxisTitle.Characters.Text = sXTitle Else .Axes(xlCategory).HasTitle = False End If If bYTitle Then .Axes(xlValue).HasTitle = True .Axes(xlValue).AxisTitle.Characters.Text = sYTitle Else .Axes(xlValue).HasTitle = False End If End With End If Next Cht Next Sht Application.ScreenUpdating = True End Sub
As before, the master chart is selected, then its formatting is applied to all of the other charts in the workbook.
Nice, we’ve changed all of the formats while retaining the axis and chart titles. Instead of three minutes to reformat each chart (longer in 2007 because the F4 shortcut key for Repeat Last Action is no longer an effective timesaver), the whole process took seconds. Maybe a minute to copy and paste this code.
Copy and Paste Chart Formatting in VBA but Keep Original Titles and Axis Scales
To apply chart formatting without changing titles and axis scales, use this modification (changes in blue):
Sub CopyChartFormatsNotTitlesOrScales()
Dim Sht As Worksheet
Dim Cht As ChartObject
Dim chtMaster As Chart
Dim bTitle As Boolean
Dim bXTitle As Boolean
Dim bYTitle As Boolean
Dim sTitle As String
Dim sXTitle As String
Dim sYTitle As String
Dim bMaximumXScaleIsAuto As Boolean
Dim bMinimumXScaleIsAuto As Boolean
Dim bMajorXUnitIsAuto As Boolean
Dim bMaximumYScaleIsAuto As Boolean
Dim bMinimumYScaleIsAuto As Boolean
Dim bMajorYUnitIsAuto As Boolean
Dim dMaximumXScale As Double
Dim dMinimumXScale As Double
Dim dMajorXUnit As Double
Dim dMaximumYScale As Double
Dim dMinimumYScale As Double
Dim dMajorYUnit As Double
Application.ScreenUpdating = False
Set chtMaster = ActiveChart
For Each Sht In ActiveWorkbook.Worksheets
Sht.Activate
For Each Cht In Sht.ChartObjects
If Sht.Name = chtMaster.Parent.Parent.Name And _
Cht.Name = chtMaster.Parent.Name Then
' don't waste time on chtMaster
Else
With Cht.Chart
' get chart info
bTitle = .HasTitle
If bTitle Then
sTitle = .ChartTitle.Characters.Text
End If
If .HasAxis(xlCategory) Then
With .Axes(xlCategory)
bXTitle = .HasTitle
If bXTitle Then
sXTitle = .AxisTitle.Characters.Text
End If
bMaximumXScaleIsAuto = .MaximumScaleIsAuto
If Not bMaximumXScaleIsAuto Then
dMaximumXScale = .MaximumScale
End If
bMinimumXScaleIsAuto = .MinimumScaleIsAuto
If Not bMinimumXScaleIsAuto Then
dMinimumXScale = .MinimumScale
End If
bMajorXUnitIsAuto = .MajorUnitIsAuto
If Not bMajorXUnitIsAuto Then
dMajorXUnit = .MajorUnit
End If
End With
End If
If .HasAxis(xlValue) Then
With .Axes(xlValue)
bYTitle = .HasTitle
If bYTitle Then
sYTitle = .AxisTitle.Characters.Text
End If
bMaximumYScaleIsAuto = .MaximumScaleIsAuto
If Not bMaximumYScaleIsAuto Then
dMaximumYScale = .MaximumScale
End If
bMinimumYScaleIsAuto = .MinimumScaleIsAuto
If Not bMinimumYScaleIsAuto Then
dMinimumYScale = .MinimumScale
End If
bMajorYUnitIsAuto = .MajorUnitIsAuto
If Not bMajorYUnitIsAuto Then
dMajorYUnit = .MajorUnit
End If
End With
End If
' copy formats
chtMaster.ChartArea.Copy
' apply formats
' NOTE: this doesn't work properly in Excel 2007 onwards
' it behaves like xlPasteAll
'.Paste Type:=xlFormats
' use this unintuitive approach instead
.ChartArea.Select
ActiveSheet.PasteSpecial Format:=2
' restore chart info
If bTitle Then
.HasTitle = True
.ChartTitle.Characters.Text = sTitle
Else
.HasTitle = False
End If
If .HasAxis(xlCategory) Then
With .Axes(xlCategory)
If bXTitle Then
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).AxisTitle.Characters.Text = sXTitle
Else
.Axes(xlCategory).HasTitle = False
End If
If Not bMaximumXScaleIsAuto Then
.MaximumScale = dMaximumXScale
End If
If Not bMinimumXScaleIsAuto Then
.MinimumScale = dMinimumXScale
End If
If Not bMajorXUnitIsAuto Then
.MajorUnit = dMajorXUnit
End If
End With
End If
If .HasAxis(xlValue) Then
With .Axes(xlValue)
If bYTitle Then
.Axes(xlValue).HasTitle = True
.Axes(xlValue).AxisTitle.Characters.Text = sYTitle
Else
.Axes(xlValue).HasTitle = False
End If
If Not bMaximumYScaleIsAuto Then
.MaximumScale = dMaximumYScale
End If
If Not bMinimumYScaleIsAuto Then
.MinimumScale = dMinimumYScale
End If
If Not bMajorYUnitIsAuto Then
.MajorUnit = dMajorYUnit
End If
End With
End If
End With
End If
Next Cht
Next Sht
chtMaster.Parent.Parent.Activate
chtMaster.ChartArea.Select
Application.ScreenUpdating = True
End Sub
