Making two pie charts look alike

[Raj]

Hi,

Data in two excel sheets is converted into pie charts on the same
sheet. I make changes to one pie chart to meet my requirements. I want
to make the second pie chart look exactly like the first. Is there a
UI way to do this (something like the format painter) or any VBA code
that I can use to make the second chart look exactly like the first
(colours, size, data categories display, borders etc)


Thanks in advance for the help


Regards,
Raj

 

[Peter T]

Copy
Edit menu, PasteSpecial Formats

Regards,
Peter T

 

[Raj]

Hi Peter,

Thanks a ton.

If it is not a bother, what would be the VBA code for copying the
format from one pie chart and applying it to other charts in the same
workbook?

Regards,
Raj

 

[Jon Peltier]

Turn on the macro recorder, carry out the action, and read the code.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


Hi Peter,

Thanks a ton.

If it is not a bother, what would be the VBA code for copying the
format from one pie chart and applying it to other charts in the same
workbook?

Regards,
Raj

 

[Peter T]

Have a go with this, and note the warning message. Might be an idea to save
a backup first.

Sub CopyChartFormats()
Dim bFlag As Boolean
Dim i As Long, idx As Long
Dim ws As Worksheet
Dim chtObj As ChartObject
Dim chtSource As Chart

On Error Resume Next
Set chtSource = ActiveChart
On Error GoTo 0

If chtSource Is Nothing Then
MsgBox "no chart selected"
Exit Sub
End If

If MsgBox("Are you REALLY sure you want to copy " & _
"formats of selected chart to " & _
"ALL embedded charts on " & _
"ALL worksheets", vbYesNo) <> vbYes Then
Exit Sub
End If

chtSource.ChartArea.Copy
idx = chtSource.Parent.Index

For Each ws In ActiveWorkbook.Worksheets
bFlag = ws.Name = chtSource.Parent.Parent.Name
Debug.Print ws.Name, chtSource.Parent.Parent.Name
For i = 1 To ws.ChartObjects.Count
If bFlag And i = idx Then
' the source chart
Else
ws.ChartObjects(i).Chart.Paste Type:=xlFormats
End If
Next
Next

End Sub

As written only processes embedded charts on worksheets.

Regards,
Peter T


Hi Peter,

Thanks a ton.

If it is not a bother, what would be the VBA code for copying the
format from one pie chart and applying it to other charts in the same
workbook?

Regards,
Raj