M
mtonkovich
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 Where:=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
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 Where:=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