Page 254 - Excel Progamming Weekend Crash Course

n540629 ch18.qxd 9/2/03 9:35 AM Page 229

Session 18 — Advanced Charting Techniques 229

‘ Copies all embedded charts in the current
‘ workbook to a new worksheet with the
‘ specified name. The copied charts have
‘ the specified width and height
‘ and are arranged in a single column.
‘ The vertical space between charts.
Const SPACE_BETWEEN_CHARTS = 20
Dim newWS As Worksheet
Dim oldWS As Worksheet
Dim co As ChartObject
Dim yPos As Integer
Dim count As Integer
‘ Turn screen updating off so screen does
‘ not flicker as charts are copied.
Application.ScreenUpdating = False
‘ Add and name the new worksheet.
Set newWS = Worksheets.Add
newWS.name = name

For Each oldWS In Worksheets
‘ Do not copy from the new worksheet.
If oldWS.name <> name Then
‘ Loop thru the ChartObjects on
‘ this worksheet.
For Each co In oldWS.ChartObjects
‘ Copy to the clipboard.
co.Copy
newWS.Range(“A1”).Select
‘ Paste into new sheet.
newWS.Paste
Next
End If
Next
‘ Position and size the charts.
count = 0
For Each co In newWS.ChartObjects
co.width = width
co.height = height
‘ Distance from left edge of sheet.
co.Left = 30
Continued