Spending too much time printing multiple charts? Use this simple macro
by Jonathan Rabson
Application:
Microsoft Excel 2000/2002/2003/2004/2007
Operating Systems:
Microsoft Windows, Macintosh
Printing charts or reports for many data sets can be time-consuming — especially if you resort to the manual method, repeatedly creating the same worksheet with different data, and then printing the results. Fortunately, you can automate the process with a VBA macro.
To efficiently print charts from several data sets with a macro, we’ll:
• Create separate worksheets for controls, data, and formatting, and a staging area for linking the data to the final output to facilitate our data processing.
• Create the macro and control it with a simple user interface on a worksheet.
• Pass values from the worksheet to the macro.
• Copy and paste worksheet data and then print automatically, all with VBA commands.
• Make the printing macro robust by adding features that make it efficient and allow it to handle errors gracefully.
After you create a great-looking worksheet, there’s a good chance you’ll need to print charts for multiple data sets. This is particularly common if you’re analyzing research data, reporting on surveys, or presenting financial reports to management for various accounts. If you have dozens of groups to report on, you might spend hours copying and pasting the data into a chart, and then printing it all out. Instead we’ll show you a VBA macro you can use to automatically print out charts many times over, each time with different data.
The printing macro in a nutshell
Before we get into the details, let’s focus on the overall idea of the macro we create in this article. Its two main steps are to:
1. Copy and paste source data for each data set automatically, so the chart and other formulas on your worksheet refer to the correct data.
2. Print the worksheet containing your chart.
Organize your workbook for automatic processing
The most important part of this technique (or any technique for manipulating data) is setting up the data in a way that’s clean and flexible. We find it useful to organize our work around the following four tasks:
• Controls. Once you create your macro, you’ll need a convenient way to run it. One of the easiest ways is to create a worksheet just for this purpose.
• Data. You may choose to store your raw data in a separate workbook, or in a database format, such as Microsoft Access files. However, for simplicity’s sake, we’ll put it in the same workbook.
• Template. No matter what sort of reporting you’re doing, you’ll want to assign a separate place for formatting. This template will include any charts and other elements you want to print.
• Staging area. Excel lets you use worksheet formulas and other features to greatly reduce the amount of work your macros need to do.
Now that you understand the four parts of our workbook’s organization, open a new workbook and set aside a separate worksheet for all of these tasks. Name them CONTROLS, DATA, TEMPLATE, and STAGE, as shown in Figure A.
Set up the data
Figure A shows a typical example of how you might set up the data in the DATA worksheet. Our example assumes that the data for each chart consists of a single row from Columns A through H.
A:
We’ll start with a worksheet of data where each row contains all the data we need to print one chart.
Use a staging area to create the chart
Unfortunately, you can’t create your chart directly from the data in the DATA worksheet because the chart will show data for only one row. Instead, paste a row of data from the DATA worksheet into the STAGE worksheet, as shown in Figure B. You can use this to produce your chart. Then, the macro can continue pasting data from the DATA worksheet to the STAGE worksheet so the chart changes automatically.
B:
Don’t link the chart and formulas to the data directly; use a staging area instead.
Go to town with the chart
Now that the data is all set up, you can create your formatted chart in the TEMPLATE worksheet. We show a bare-bones example in Figure C. Just make sure that all the data for the chart comes from the STAGE worksheet; don’t include any hard-coded values or links to the DATA worksheet.
To set up the TEMPLATE worksheet:
1. Apply any text and formatting you desire to introduce your chart, including a heading and data identification, as shown in Figure C.
2. Provide cell references to the STAGE worksheet for any text through which the macro passes in the data sets you want to print. In cell D3, for example, you can see the formula we entered in the formula bar, as shown in Figure C. Cell D4 contains the formula =STAGE!A2.
3. Click on the STAGE worksheet tab and select the cells for your chart (for our example, we used cells D1:H2).
4. Click the Chart Wizard button chart wizard to access the Chart Wizard, create a simple column chart, and then format your chart in any way you desire. Place it as an object in the TEMPLATE worksheet. (In 2007, select the Insert tab and choose Column in the Charts area.)
C:
The TEMPLATE worksheet links to the data on the STAGE worksheet.
Include basic inf
