Macro 81: Print All Charts on a Worksheet
To print a chart, you can click any embedded chart in your worksheet and then click Print. This prints
the chart on its own sheet without any of the other data on the sheet. This sounds easy enough, but
it can become a chore if you’ve got to do this for many charts. This macro makes short work of this
task.
How it works
All charts belong to the ChartObjects collection. To take an action on all charts at one time, you
simply iterate through all the charts in ChartObjects. Each chart in the ChartObjects collec-
tion has an index number that you can use to bring it into focus. For example, ChartObjects(1)
points to the first chart in the sheet.
In this macro, we use this concept to loop through the charts on the active sheet with a simple coun- ter.
Each time a new chart is brought into focus, print it.
Sub Macro81()
‘Step 1: Declare your variables
Dim ChartList As Integer
Dim i As Integer
‘Step 2: Start Looping through all the charts
For i = 1 To ActiveSheet.ChartObjects.Count
‘Step 3: Activate each chart and print
ActiveSheet.ChartObjects(i).Activate
ActiveChart.PageSetup.Orientation = xlLandscape
ActiveChart.PrintOut Copies:=1
‘Step 4: Increment to move to next chart
Next i
End Sub
1. Step 1 declares an integer object that is used as a looping mechanism. We call the
variable i.
2. Step 2 starts the looping by setting i to count from 1 to the maximum number of charts in
the ChartObjects collection on the active sheet. When the code starts, i initiates with
the number 1. As we loop, the variable increments up one number until it reaches a number
equal to the maximum number of charts on the sheet.
3. Step 3 passes i to the ChartObjects collection as the index number. This brings a chart
into focus. We then use the ActiveChart.Printout method to trigger the print. Note
that you can adjust the Orientation property to either xlLandscape or xlPortrait
depending on what you need.
4. Step 4 loops back around to increment i up one number and get the next chart. After all
charts have been evaluated, the macro ends.
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.
