Microsoft Office Tutorials and References
In Depth Information
Creating a chart from data on different worksheets
Next, a ChartObject object is added, using the Add method of the ChartObjects
collection. Note that the recorded macro used the Add method of the Charts
collection. This added a new Chart sheet, which was eventually relocated to the worksheet.
Adding a new ChartObject is a more efficient approach because you can specify
the size and position (as arguments) when it’s created.
The remainder of the code sets various properties of the Chart object contained
in the ChartObject object. The final statement ( Parent.Select ) selects the created
ChartObject .
This basic macro can be modified in a number of ways. For example, you can
specify a different chart type, set colors, add or remove gridlines, and so on.
Creating a chart from data on different worksheets
The example in this section uses a workbook that contains five worksheets. The
CreateFiveCharts macro creates an embedded chart from the data on each sheet.
One slight hitch is that the data in each sheet is not consistent — the sheets vary in
the number of data rows, and each chart needs to use the data in the last row.
Figure 16-9 shows one of the worksheets (the others are similar but have a
different number of rows).
Figure 16-9: One of five worksheets that will be used
to create a chart
Although the following macro may seem complicated, it’s really very
straightforward. It loops through the sheets, creates a chart, and applies several types of
customizations to the chart. The appropriate objects and properties were determined by
using the macro recorder, and then the recorded code was incorporated into the
macro. In other words, this macro was created in a number of small steps.
Sub CreateFiveCharts()
Dim ChtObj As ChartObject
Dim ChtTop As Long, ChtLeft As Long
Dim ChtHeight As Long, ChtWidth As Long
Dim Sht As Worksheet
Dim LastRow As Long
ChtTop = 1
Search JabSto ::




Custom Search