VBA Code - Update Pivot Chart Source with Variable Row

[Angela1982]

I am still fairly new to VBA. I have been using S & LRGLOBAL throughout the rest of my Macros and would like to also use it for updating my pivotchart sources. However, I can't get it to update the chart even if I name it "S266", so I think it may be another part of the code that is stopping it from working. Any advice, please?

Sub Pivot_Tables()
'
' Pivot_Tables Macro
'
Sheets("GlobalListings").Select
Dim LRGLOBAL As Long
'For understanding LRGLOBAL = Last Row
LRGLOBAL = Cells(Rows.Count, 1).End(xlUp).Row

Sheets("Leahs Dashboard").Select

Charts(Global_PieChart).SetSourceData Source:=Sheets(GlobalListings).Range("A1:S" & LRGLOBAL)


End Sub

 

[Alex Blakenburg]

Give this a try:

Sub Pivot_Tables()
'
' Pivot_Tables Macro
'
Sheets("GlobalListings").Select
Dim LRGLOBAL As Long
'For understanding LRGLOBAL = Last Row
LRGLOBAL = Cells(Rows.Count, 1).End(xlUp).Row

Dim GlobalRng As Range
Set GlobalRng = Range("A1:B" & LRGLOBAL)

Sheets("Leahs Dashboard").Select
ActiveSheet.ChartObjects("Global_PieChart").Chart.SetSourceData Source:=GlobalRng

End Sub

 

[Angela1982]

Thanks Alex, the code still stops at the ActiveSheet.ChartObjects line and I can't figure out why. I tried to record myself updating the chart as well. Every time I do, it only will not record the update.source command. This is all it shows: after I have stopped recording:

Sub Testing3()
'
' Testing3 Macro
'

'
ActiveSheet.ChartObjects("Global_PieChart").Activate
ActiveChart.FullSeriesCollection(1).Select
End Sub

I once had a version of excel that refused to recognize the Range.AutoFilter code. Could something like that be my issue?

 

[Alex Blakenburg]

Both your initial Sub Name and your recorded code indidate that you are using a Pivot Chart and not a regular chart.

Can you try @Jon Peltier's macro that you will find at the link below.
You will need to put in your Pivot Table name on the "Set pt =" line and your Chart name on the "Set cht =" line

Dynamic Chart using Pivot Table and VBA - Peltier Tech