Excel Easy 1

Tuesday, April 2, 2019

14. [Word Excel VBA] Change Charts data automatically by VBA

Sub Change_Data()
Dim i, j As Long
i = Sheets("Monthly5StarData").Range("A1000").End(xlUp).Row
For i1 = i To 4 Step -1
If Sheets("Monthly5StarData").Range("H" & i1) <> "" Then
j = i1
Exit For
End If
Next

Sheet1.ChartObjects("Chart 1").Activate
ActiveChart.FullSeriesCollection(1).XValues = "=Monthly5StarData!$A$4:$A" & i
Sheet1.ChartObjects("Chart 18").Activate
ActiveChart.SetSourceData Source:=Sheets("Monthly5StarData").Range("A3:A" & j & ",X3:X" & j)
Sheet1.ChartObjects("Chart 2").Activate
ActiveChart.SetSourceData Source:=Sheets("Monthly5StarData").Range("A3:A" & j & ",H3:H" & j)
End Sub