How to print chart sheets only?

[pixel8]

Hello colleagues,

I'm working on 10 excel files for a client, and having trouble figuring out
how to print them the way I want to. Any help would be greatly appreciated.

Each file contains one large data worksheet, which prints out 17 pages, plus
several charts, each on its own sheet, created from various parts of the
data worksheet. There are from 5 to 20 separate chart sheets in each file. I
want to print JUST the chart sheets in each file, and NOT the 17 pages of
data.

The only way I can figure doing this is to open ALL the charts, one at a
time, and choose "Active Sheet" to print it out. But this will be
time-consuming and very annoying. Does anyone know of a way to print just
the chart sheets, skipping the data worksheet?

Thanks very very much!!

Melinda

 

[Debra Dalgleish]

The following code will print (or preview) the chart sheets:

Sub PrintCharts()
Dim ch As Chart
For Each ch In ActiveWorkbook.Charts
ch.PrintPreview 'for testing
' ch.PrintOut 'for printing
Next
End Sub

 

[pixel8]

Debra, thanks for your prompt reply! I haven't used code in Excel before,
is it a huge question to ask how and where to insert code like this or can
you point me to an article or link that tells how?

Thanks very much,
Melinda

 

[Debra Dalgleish]

Melinda,

To insert and use this code:

1. Copy the code from my previous message
2. In Excel, press Alt+F11, to open the Visual Basic Editor
3. Choose Insert>Module
4. The line:
' ch.PrintOut 'for printing
should appear in green text, because the apostrophe at the
beginning of the line makes it a comment, rather than
code that should be run.
5. The line:
ch.PrintPreview 'for testing
is in black text, and will show a preview of each chart sheet
6. After you test the macro (instructions to follow), you can
remove the apostrophe from the PrintOut line, and
add an apostrophe at the beginning of the PrintPreview line.
7. Choose File>Close and return to Microsoft Excel

To run the macro:
1. Choose Tools>Macro>Macros
2. Select the PrintCharts macro
3. Click Run

For more information on macros, see David McRitchie's list of VBA tutorials:

http://www.mvps.org/dmcritchie/excel/excel.htm#tutorials

It includes this link, which explains how to assign a macro to a button,
or toolbar:


http://support.microsoft.com/default.aspx?scid=/support/excel/content/vba101/default.asp

Debra

 

[pixel8]

WOW!! Worked like a charm, thank you!!!!!!
- Melinda