VBA Help: Automatically Update Chart Series for New Time Periods in Investing Spreadsheet

[mflatt23]

Hello Excel community,

I've created an investing spreadsheet to track quarterly and yearly KPIs for companies I own. I'm looking for assistance with a VBA issue I'm encountering.

Current Setup​

• Two tabs: Quarterly and Yearly
• Each tab has charts that display KPI data over time
• VBA scripts for each tab to update charts when new time periods are added

What's Working​

• The VBA successfully updates the X-axis (time periods) when I add a new quarter or year

The Problem​

• The Y-axis (data series) is not updating correctly
• It seems the series values are not maintaining locked rows while extending the column for the new date

Desired Outcome​

When I add a new quarter or year:

1. Charts should automatically update to include the new time period
2. Both X-axis (time) and Y-axis (data) should update correctly

I've attached the Excel file for reference. Any insights or solutions would be greatly appreciated!

Excel file: Download KPI Tracking.xlsm | LimeWire

 

[myall_blues]

It's a little hard to tell exactly what you want as there is no 'data' for the graphs, but your problem appears to be with the line indicated in the red box below.

If I add 2025 to the end of the years and step your code through (for the year update), then I get the following results for the value of series.Formula.

Chart object	series.Formula before update	series.Formula after update
Object 1	=SERIES(Yearly!$B$8,Yearly!$C$6:$H$6,Yearly!$C$8:$H$8,1)	=SERIES(Yearly!$B$8,Yearly!$C$6:$I$6,Yearly!$C$15:$I$15,1)
Object 2	=SERIES(Yearly!$B$57,Yearly!$C$6:$H$6,Yearly!$C$57:$H$57,1)	=SERIES(Yearly!$B$57,Yearly!$C$6:$I$6,Yearly!$C$15:$I$15,1)
Object 3	=SERIES(Yearly!$B$59,Yearly!$C$6:$H$6,Yearly!$C$59:$H$59,1)	=SERIES(Yearly!$B$59,Yearly!$C$6:$I$6,Yearly!$C$15:$I$15,1)

... and so on.
As you can see the Y part of the series formula after the update becomes identical for all the graphs.
Hope this helps

 

[mflatt23]

Great call out, I can fill it with data if needed, but it might be best explained with an example. Take chart Total Revenue ($M) row 74. When I add a new quarter to column S for Q1 '25, I want that chart to update for the new data, so for Q1 '25 quarter and the revenue for that quarter.

With the code I previously got to, it updates the axis label range correctly, but messes up series values part (I wasn't sure how to code it where it just keeps everything the same (e.g., =Quarterly!$C$7:$R$7) except make the R an S. Series name would also stay the same in every chart. I then wanted this for all the charts on the Quarterly Tab and then I have a separate VBA for those on the Yearly tab. Feel like I got most of the way there but not 100% lol

 

[myall_blues]

Maybe this way works better for you.

Sub UpdateYearlyChartsToLatestYear()
    Dim chartObj As ChartObject
    Dim updatedCount As Integer
    Dim lastColNum As Long, lOccur As Long
    Dim series As series
    Dim lastCol As String, seriesFormula As String, lastYear As String
    Dim seriesArr As Variant
    Dim ws As Worksheet
    
    ' Initialize the updated chart counter
    updatedCount = 0
    
    ' Set the worksheet object
    Set ws = ThisWorkbook.Sheets("Yearly")
    
    ' Find the last column with data in row 6 (year headers are in row 6) and convert to letter
    lastColNum = ws.Cells(6, ActiveSheet.Columns.Count).End(xlToLeft).Column
    lastCol = Col_Letter(lastColNum)
    ' Get the latest year from row 6 (e.g., 2024)
    lastYear = ws.Cells(6, lastCol).Value
    
    ' Loop through each chart in the "Yearly" sheet and update the data range
    For Each chartObj In ws.ChartObjects
        ' Loop through each series in the chart and update both values and axis labels
        For Each series In chartObj.Chart.SeriesCollection
            ' Get the current formula for the series and place components in an array
            seriesFormula = Replace(series.Formula, ":", ",")
            seriesArr = Split(seriesFormula, ",")
            ' Modify required array elements
            lOccur = InStrRev(seriesArr(2), "$") + 1
            seriesArr(2) = "$" & lastCol & "$" & Mid(seriesArr(2), lOccur)
            lOccur = InStrRev(seriesArr(4), "$") + 1
            seriesArr(4) = "$" & lastCol & "$" & Mid(seriesArr(4), lOccur)
            ' Recombine the array
            seriesFormula = seriesArr(0) & "," & seriesArr(1) & ":" & seriesArr(2) & "," & seriesArr(3) & ":" & seriesArr(4) & "," & seriesArr(5)
            ' Reassign the series formula
            series.Formula = seriesFormula
        Next series
        
        ' Increment the updated chart counter
        updatedCount = updatedCount + 1
    Next chartObj
    
    ' Display a message box confirming the update, along with the number of updated charts
    MsgBox updatedCount & " charts have been updated to include the latest year (" & lastYear & ").", vbInformation
End Sub
Public Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function

 

[mflatt23]

THANK YOU!!!

 

[myall_blues]

THANK YOU!!!

You’re very welcome.