Orfil
New Member
Hi there
The attached workbook is based on Jon Peltier's blog post 'Create and Update a Chart Using Only Part of a Pivot Table's Data.' I added loops that cycle through four charts, updating them with their corresponding field(s).
My problem is that these are not nested loops in the sense that they run one inside the other: these loops run side-by-side. How do I step outside the one loop long enough to load a new string from the other loop?
Here is the code as it stands
Are for-each loops the best choice for this situation? Is this even the best approach to updating charts? I like it but would range names be better (faster)? What if you have a much larger dataset and a couple of dozen charts?
I'm way out of my depth here so any help/suggestions/advice/remarks would be more than welcome
Rgds+bestwishes, Orfil
The attached workbook is based on Jon Peltier's blog post 'Create and Update a Chart Using Only Part of a Pivot Table's Data.' I added loops that cycle through four charts, updating them with their corresponding field(s).
My problem is that these are not nested loops in the sense that they run one inside the other: these loops run side-by-side. How do I step outside the one loop long enough to load a new string from the other loop?
Here is the code as it stands
Code:
Sub UpdateSalesRepCharts()
Dim cht As Chart
Dim pt As PivotTable
Dim sSrsFmla As String
Dim SalesDataFlds As Variant, SalesDataChts As Variant
Dim sdc As Variant, sdf As Variant
Dim rXVals As Range, rYVals As Range, rName As Range
SalesDataChts = Array("chtSalesRep01", "chtSalesRep02", "chtSalesRep03", "chtSalesRep04")
SalesDataFlds = Array("SalesRep01", "SalesRep02", "SalesRep03", "SalesRep04")
Set pt = Sheets("Calc").PivotTables("ptSalesData")
With cht
For Each sdf In SalesDataFlds
On Error Resume Next
For Each sdc In SalesDataChts
On Error Resume Next
Sheets("Dashboard").ChartObjects(sdc).Activate
Set cht = ActiveChart
Set rName = pt.PivotFields("SalesRep").PivotItems(sdf).LabelRange
Set rXVals = Union(pt.PivotFields("MonthNames").DataRange, _
pt.PivotFields("Years").DataRange)
Set rYVals = Intersect(pt.PivotFields("SalesRep").PivotItems(sdf).DataRange, _
pt.TableRange1.EntireRow)
sSrsFmla = "=series(" & rName.Address(, , , True) & "," & _
rXVals.Address(, , , True) & "," & rYVals.Address(, , , True) & ",1)"
With cht
' clear out chart data
Do
If .SeriesCollection.Count = 0 Then Exit Do
.SeriesCollection(1).Delete
Loop
' add new series with defined data
With .SeriesCollection.NewSeries
.Formula = sSrsFmla
End With
End With
Next sdc
Next sdf
End With
End SubAre for-each loops the best choice for this situation? Is this even the best approach to updating charts? I like it but would range names be better (faster)? What if you have a much larger dataset and a couple of dozen charts?
I'm way out of my depth here so any help/suggestions/advice/remarks would be more than welcome
Rgds+bestwishes, Orfil