Page 255 - Excel Progamming Weekend Crash Course

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

230 Saturday Evening

Listing 18-1 Continued
co.Top = count * (height + SPACE_BETWEEN_CHARTS) +
SPACE_BETWEEN_CHARTS
count = count + 1
Next

‘ Turn screen updating back on.
Application.ScreenUpdating = True

End Sub
Public Sub TestCopyEmbeddedCharts()

CopyEmbeddedChartsToNewSheet “All Charts”, 240, 140
End Sub

Note that the program sets the ScreenUpdating property to False to prevent flickering
while the charts are being copied and resized. Of course, this property must be set back to
True at the end of the program so the changes become visible.
You may be wondering why the code selects cell A1 in the new workbook before each
paste operation. This is because when you paste a chart into a workbook, the just-pasted
chart is selected. When you then try to paste the next chart, Excel thinks you are trying
to paste it into the selected chart and generates an error message. By selecting a worksheet
cell, the paste operation has the worksheet as its target and works without a problem.
Because the CopyEmbeddedChartsToNewSheet procedure takes arguments, you cannot
run it directly but must call it from another procedure. Listing 18-1 includes this procedure,
named TestCopyEmbeddedCharts. You may want to modify the arguments to the procedure
call — as written, it names the new worksheet All Charts and sizes the copied charts at 240
× 140 points. To try out this program:

1. Copy the code from Listing 18-1 to a module in the VBA Editor.
2. Edit the code in the TestCopyEmbeddedCharts procedure if desired (to change the
arguments).
3. Open or create a workbook that contains several embedded charts, ideally on two
or more worksheets.
4. Press Alt+F8 to open the Macros dialog box.
5. Select the macro TestCopyEmbeddedCharts and then click Run.

Figure 18-1 shows an example of a worksheet created by this program, showing several
copied charts.