Microsoft Office Tutorials and References
In Depth Information
Looping through All Embedded Charts
You can quickly move all chart sheets to their own workbook. For example, check out the
following example that creates a new workbook and relocates the chart sheets before Sheet1 in that new
‘Declare variable for your active workbook name.
Dim myName As String
‘Define the name of your workbook.
myName = ActiveWorkbook.Name
‘Add a new Excel workbook.
‘Copy the chart sheets from your source workbook
‘to the new workbook.
looping THrougH All EMBEddEd cHArTs
Suppose you want to do something to every embedded chart in your workbook. For example, if
some charts were originally created with different background colors, you might want to
standardize the look of all charts to have the same color scheme. The following macro shows how to loop
through every chart on every worksheet to format the chart area with a standard color of light blue:
‘Turn off ScreenUpdating.
Application.ScreenUpdating = False
‘Declare variables for worksheet and chart objects.
Dim wks As Worksheet, ChObj As ChartObject
‘Open loop for every worksheet.
For Each wks In Worksheets
‘Determine if the worksheet has at least one chart.
If wks.ChartObjects.Count > 0 Then
‘If the worksheet has a chart, activate the worksheet.
‘Loop through each chart object.
For Each ChObj In ActiveSheet.ChartObjects
‘Activate the chart
‘Color the chart area blue.
ActiveChart.ChartArea.Interior.ColorIndex = 8
‘Deselect the active chart before proceeding to the
‘next chart or the next worksheet.