Macro 80: Create a Set of Disconnected Charts
When you need to copy charts from a workbook and paste them elsewhere (another workbook,
PowerPoint, Outlook, and so on), it’s often best to disconnect them from the original source data.
This way, you won’t get any of the annoying missing link messages that Excel throws. This macro
copies all of the charts in the active sheet, pastes them into a new workbook, and disconnects them
from the original source data.
How it works
This macro uses the ShapeRange.Group method to group all the charts on the active sheet into
one shape. This is similar to what you would do if you were to group a set of shapes manually. After
the charts are grouped, we copy the group and paste it to a new workbook. We then use the
BreakLink method to remove references to the original source data. When we do this, Excel
hard-codes the chart data into array formulas.
Sub Macro80()
‘Step 1: Declare your variables
Dim wbLinks As Variant
‘Step 2: Group the charts, copy the group, and then ungroup
With ActiveSheet.ChartObjects.ShapeRange.Group
.Copy
.Ungroup
End With
‘Step 3: Paste into a new workbook and ungroup
Workbooks.Add.Sheets(1).Paste
Selection.ShapeRange.Ungroup
‘Step 4: Break the links
wbLinks =
ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
ActiveWorkbook.BreakLink Name:=wbLinks(1), _
Type:=xlLinkTypeExcelLinks
End Sub
1. Step 1 declares the wbLinks variant variable. The macro uses this in Step 4 to pass the link
source when breaking the links.
2. Step 2 uses ChartObjects.ShapeRange.Group to group all the charts into a single
shape. The macro then copies the group to the clipboard. After the group is copied, the
macro ungroups the charts.
3. Step 3 creates a new workbook and pastes the copied group to Sheet 1. After the group has
been pasted, we can ungroup so that each chart is separate again. Note that the newly cre-
ated workbook is now the active object, so all references to ActiveWorkbook point back to
this workbook.
4. Step 4 captures the link source in the wbLinks variable. The macro then tells Excel to break
the links.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code into the newly created Module.
