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
Jon Peltier says
Ray –
Yep, everyone has their own requirements, I guess.
To do just the activesheet, replace this:
For Each Sht In ActiveWorkbook.Worksheets
with this:
Set Sht = ActiveSheet
and remove this:
Next Sht
Other options need similar changes. I imagine choosing both an active chart as the master chart and choosing only some charts to reformat would be a challenge, requiring some kind of dialog system.
Ray says
Hi Jon –
Great tip, VERY useful … but what if you don’t want to change *every* chart in the *whole* workbook? Maybe just the selected charts or just the ones the active-sheet? A small pop-up box to solicit user-preferences perhaps?
Thanks alot!
//ray
JP says
“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.”
:)
I wonder if a response or followup from the “OP” is forthcoming.
Mike Alexander says
Nice one Jon. You should make this part of the dashboarding bootcamp! VBA day.
AdamV says
Nice piece of code – as you say, this could be a jumping-off point for people with their own specific requirements.
For example, you could change the name of one chart to “Master” or somesuch, then find this one to copy formats from, and iterate through all the selected charts to apply the formats to.
This does seem to be one of the places where F4 (or CTRL-Y) seems to work just fine, as the last action is the paste special, so you are not plagued with the issues which beset the non-modal formatting dialog boxes.
John Mansfield says
Very nice Jon. I tend to use worksheet cells for titles and totally missed that one.
I didn’t think I wrote the procedure very well to begin with and am glad you were able to improve it.
Thanks again.
Patricio Cuaron says
Great code, but in xl2007 sp2 it copies the format AND the data of the charts. Can’t figure out why yet.
Jon Peltier says
Patricio –
This happens because Excel 2007 SP2 apparently has a bug. It doesn’t properly recognize xlFormats or xlPasteFormats. This bug is also present in Excel 2007 SP1 and presumably all Excel 2007 versions.
Bruce Glanville says
Trying to use this in Excel 2010 but I keep getting a run time error 1004 on the .Paste Type = xlFormats statement.
I created a ~myuserpathxlstartpersonal.xlsm file and copied the VBA into a macro in that file, saved then hid the window per MS article here:
In the workbook with the charts I want to format, I select the chart I want to copy the format from (a two data point pie chart) and run the macro. Then I get the run time error. I am a complete noob to VBA so having a tough time figuring this out. I looked up the chart.paste method and saw that valid paste types are xlPasteXxxxx so I changed xlFormats to xlPasteFormats but that was apparently not the issue.
Any comments or suggestions welcome
Jon Peltier says
What is the error message? The error number doesn’t help much, since error number 1004 is applied to half the run time errors you’ll encounter.
In Excel 2010 I don’t get an error, but I still get the data pasted as well as the formats. And the data becomes new series in front of the existing series in the target chart. So we have to transfer the data from the pre-existing series to the inserted, then remove the extraneous series. This does it for uncomplicated data series (tested minimally), though if the target chart has more series than the original chart, the series strangely end up in the wrong order.
Martin Larsen says
A lot of scientist uses a graph type available in software like Graph Pad Prism but not in Excel as far as i know. The graph type depicts Y-values grouped in categories spread on the x-axis. Contrary to bar diagrams of the average of the Y-values (possible with error-bars), it will however depict each Y-value of the dataset. To avoid that points are placed on top of each other the points are automatically scattered a long the vertical axis when multiple points are present around the same y value. An example is figure 2E in the following manuscript http://www.plosone.org/article/info%3Adoi%2F10.1371%2Fjournal.pone.0042403.
I have thought of depicting data as an x-y scatter plot, representing each category as an x-value (1, 2, 3 etc.). Then changing the x-value with a delta-value (plus/minus), when multiple Y-values are similar. However, even doing this would not render the data categorical, and one would not be able to easily change the order of the categories. And more importantly it could be perfect to overlay this type of figures with a categorical plot showing the median and the standard deviation. However, overlaying two plots where one has a numerical x-axis and the other a categorical is not feasible I believe.
I would be very interested in any ideas that could solve this problem.
Kind regards
Martin
Jon Peltier says
Martin –
This can certainly be done. Look for it in an upcoming blog post.
Richard says
Many thanks, I have found this very useful. Is it possible to modify this code so that it works on charts that are on separate worksheets (i.e. not embedded)?
Bruce Glanville says
Sorry for not responding earlier. I ended up simply copying and pasting to get what I needed in the short term.
The error message was Method ‘Paste’ of object ‘_Chart’ failed
Still getting the error. I also find the execution of the macro is slow. It runs for several seconds before I get the error.
Dan says
I couldn’t get this working either in 2010. I get the same issue with xlFormats not being valid in 2010. I gave up on Google after a while and instead of Copy and Paste I created a VBA which applies a Chart Template to all Charts in a Workbook. Works flawlessly.
Chris says
Anybody got ideas how to copy just certain series formats between two or within one chart.
ie. Not just Color Index like this, but the whole Fomatting: (markes, line style, fore/back color, etc)
.SeriesCollection(i).Interior.ColorIndex = MasterSeries.Interior.ColorIndex
tx
Jon Peltier says
Hi Chris –
There isn’t any built-in way to copy and paste all formats of a chart element. I built a small function as part of my latest Chart Utility that copies a series formats and pastes it onto another series, but subject to inconsistencies and omissions in the Excel object model, it often doesn’t render the pasted formats very closely to the copied formats. I’ve thought about doing the same for axes.
Anonymous says
Messed up my entire workbook…
Konrad says
Hi,
I pasted the code and nothing changes. An error occurs in
.Paste Type:=xlFormats
I have MS Office 2013 – is it the cause?
Jon Peltier says
It turns out that
does not work properly in Excel 2007 onwards. No error appears, but whatever Type is specified, the paste occurs as if you used xlPasteAll.
The code that works is totally not obvious. Replace the above line with
I’d like to talk with the genius who decided PasteSpecial on the ActiveSheet should affect an embedded chart.
Konrad says
Hey Jon,
I applied your change to the code and now it looks like this:
(but still it does not work – compile error occurs – method or data member not found)
Jon Peltier says
Konrad –
I didn’t remember I had addressed this issue in a comment, and didn’t realize you were using that as the basis for your code.
Here’s the modified code from my trials yesterday, edits in blue:
Konrad says
Thank you Jon. That really helped :)
Sandra says
This has helped me enormously, thanks, but could you please also point a novice such as me in the right direction on how to modify the code if I wanted to keep the axis settings (minimum, maximum, major unit – all of which are sometimes set numerically but at other times set to the automatic value) the same as in the original charts, for the x-axis as well as for the primary and secondary y-axes?
Jon Peltier says
Sandra –
Start with the modified code from my December 18, 2014 comment. In addition to saving the text from the chart and axis titles, you have to save the axis scale information. This is a rough draft:
Sandra says
Thank you Jon, that works really well – much appreciated!
Joey M says
Hi Jon,
i am novice to vba,and trying to learn it
i want to create simple Bar Charts repeatedly .I have used your code(CopyChartFormatsNotTitles2()) for formatting the charts.but some of the formatting is not working like:
– color of each series is not getting formatted
-i have switched my rows/columns while creating Hbar chart,this also not getting mirrored ( i have added
ActiveChart.PlotBy = xlRows in your code and still it is not working)
-Legends are not getting formatted.
Let me know if am not clear about my question.
MoMi says
Hello,
First thanks for your support (I’m using macros since few hours only and your support is very helpful). I would like to use a chart as a template and copy/paste the formatting to all others but without keeping the other charts scale so I used the code of Thursday, December 18, 2014 at 7:19 am.
I saw 2 limitations at the moment:
– If the copied chart does not have a title then the master title content is copied
– If the master has less series than the copied chart, then the additional series are not formatted
I performed some tests and find the following results (not exhaustive): It copies
1) Titles (chart, axis):
– formatting (font, colors, position)
– but not titles content unless no title exist in the copied charts
2) Lines
– Thickness
– Color
– But limited to the number of series in the master
3) Axis
– Scale range if fixed
– Font
– Gridline (Color, Type (solid, dash))
4) Plot area
5) Type of chart (scatter …)
Quentin Le Bihan says
Hi Jon,
first thing first thank you for your website. It is so useful!
I did read the post, examine the code (a little bit, I just started VBA) and then copy and ran it.
Then I found out that “new data, where the **** does it come from?!” had been added to all charts.
The problem had already been addressed in the comment section but I didn’t read it of course.
(yours on Wednesday, December 17, 2014 at 1:57 pm about .Paste Type:=xlFormats occurs as xlPasteAll in excel 2007 and onwards.)
I think I am not the first to have done it (copying, running the code with not enough testing and screwing everything up)
So, for the future ones, could you please make an update of this article, with the new code inside the post please?
Thank you again
Roman says
Thanks Jon for this conceptually inspiring article as well as many other articles you share.