Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Expand Chart Macro to Plot Multiple Series
Gang - Andy Pope graciously provided the following Macro that will very
nicely generate 88 charts. It plots a single y value for each x. I would like to expand that and plot 2 or perhaps 3 y values (i.e., 3 series rather than just 1) for each x. Could someone please help me figure out how to do this? I've made a few changes to the code with no luck. Also would someone confirm the following - trying to generate nonpivot charts from a pivot table can't be done. The data that I'm trying to plot is in a Pivot table. I found that the only way to make the thing work is to get the data out of the pivot table. Is that accurate? Hi, This should get you started. I would try it on a subset of the data first rather than all 88 sets. You will still need to add any formatting code you have recorded. Sub Macro1() ' ' Macro1 Macro ' Macro recorded 05/07/2006 by Andy Pope ' Dim lngRow As Long Dim lngStartRow As Long Dim chtDeer As Chart Dim shtData As Worksheet Dim rngXData As Range Dim rngYData As Range Dim strCounty As String ' Set shtData = Worksheets("Sheet1") lngRow = 2 lngStartRow = 2 Do While shtData.Cells(lngRow, 1) < "" If shtData.Cells(lngRow, 1) < shtData.Cells(lngRow + 1, 1) Then Set rngXData = shtData.Range( _ "B" & lngStartRow & ":B" & lngRow) Set rngYData = rngXData.Offset(0, 1) strCounty = shtData.Cells(lngRow, 1).Value ' make a chart Set chtDeer = Charts.Add With chtDeer Do While .SeriesCollection.Count 0 .SeriesCollection(1).Delete Loop .ChartType = xlXYScatterLines .PlotBy = xlColumns With .SeriesCollection.NewSeries .XValues = rngXData .Values = rngYData End With .Location Whe=xlLocationAsNewSheet .HasTitle = True .ChartTitle.Characters.Text = strCounty .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False .HasLegend = False .Name = strCounty End With lngStartRow = lngRow + 1 End If lngRow = lngRow + 1 Loop Set rngXData = Nothing Set rngYData = Nothing Set shtData = Nothing Set chtDeer = Nothing End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to plot column chart with one series against multiple series. | Charts and Charting in Excel | |||
Use a macro to expand data series for chart? | Charts and Charting in Excel | |||
plot multiple time series | Charts and Charting in Excel | |||
chart data series -- plot a table as a single series | Charts and Charting in Excel | |||
Why plot area does not expand after clearing series legend? | Charts and Charting in Excel |