VBA to reset existing charts with column data from another sheet and rename legend

[rebaS]

I would greatly appreciate your help with charts. I'm new to VBA and I just don't understand how to apply information from other posts to my issue.
I have 3 line charts and 3 column charts on Sheet 1. The data on sheet 2 is by month and updated monthly; therefore, the number of rows change month to month (e.g. Feb, Mar, April). The dates are in column A, starting in row 6. My charts never update and I have to manually "Select Data" each month. I would like the charts to auto update based on the data on sheet 2.

This is my latest attempt.

Sub ResetCharts()
Dim cht As ChartObject
Dim wsData As Worksheet
Dim wsCharts As Worksheet
Dim lastRow As Integer
Dim i As Integer

Set wsData = daily
Set wsCharts = dash

lastRow = wsData.Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To wsCharts.ChartObjects.Count
Set cht = wsCharts.ChartObjects(i)
cht.Chart.SetSourceData Source:=wsData.Range("A6:C" & lastRow)
cht.Chart.FullSeriesCollection(1).Name = "aa"
cht.Chart.FullSeriesCollection(2).Name = "bb"
cht.Chart.Axes(xlCategory).CategoryType = xlCategoryScale
Next i
End Sub

 

[kevin9999]

Could you share a copy of your workbook via DropBox, Google Drive or similar file sharing platform? You can easily disguise any sensitive information.

 

[rebaS]

I apologize for taking so long to get back to you.

Google Sheets: Sign-in

Access Google Sheets with a personal Google account or Google Workspace account (for business use).

docs.google.com

 

[Peter_SSs]

@rebaS
Can you please confirm whether this is an Excel question or a Google Sheets question?