Changing chart references as the months passes

[D_Holmes]

Hi All,

I am trying to create a macro that updates three different charts with information based on data from a specific sheet. I cannot figure out what the VBA name for the specific charts are or excel is changing them as I go (not sure how that works), but the charts keep getting jumbled up. Here is the code I have written so far:

If Worksheets("09_2020").Visible = True Then

Worksheets("Dashboard").Select
Charts(1).Activate
ActiveChart.Parent.Name = "Type of Event"
With ActiveChart
.SetSourceData Source:=Worksheets("09_2020").Range("R2:S6")
.SetElement (msoElementDataLabelCallout)
.SetElement (msoElementLegendRight)
End With

Charts(2).Activate
ActiveChart.Parent.Name = "Position"
With ActiveChart
.SetSourceData Source:=Worksheets("09_2020").Range("T2:U9")
.SetElement (msoElementDataLabelCallout)
.SetElement (msoElementLegendNone)
End With

Charts(3).Activate
ActiveChart.Parent.Name = "Location"
With ActiveChart
.SetSourceData Source:=Worksheets("09_2020").Range("X2:Y6")
.SetElement (msoElementDataLabelCallout)
.SetElement (msoElementLegendNone)
End With
End If

I will create a series of the above code to reference sheets with dates til June 2021.

All help is greatly appreciated.

Thank you.

 

[C Moore]

If there are only the three, the following post will cycle through each one.

VBA to loop through all shapes on ActiveSheet and iterate their shape name by appending +1

I'd like to iterate through all shapes on an ActiveSheet (of which each shape is comprised of 3 named parts - "Background_COLORNAME", "Title_COLORNAME", and Text_COLORNAME") to rename shapes with the same exact name by appending # that iterates by (1) for each existing shape ex: "Title_Yellow1"...

www.mrexcel.com