updating row source for charts in VBA [RESOLVED]

[Br1an_g]

ok,

i have a selection of charts on a spreadsheet.

e.g. charts(1) to charts(12)

i have a combo box which has numbers 1 to 52, for each week of the month.

I want the charts row source to be updated when a week is selected from the drop down. I dont want to change the column or source sheet, just the rows.

is there any easy way to do this? or will i have to read the current source into a string and manipulate it?

i was messing about with this:
[2nd Edit to incorporate both chart plots]

For MyCount = 1 To 12
    Charts(MyCount).SeriesCollection(1).SourceData Source:=Sheets(1).Range("a1:a10"), _
    PlotBy:=xlColumns
    Charts(MyCount).SeriesCollection(2).SourceData Source:=Sheets(1).Range("a1:a10"), _
    PlotBy:=xlColumns
Next

[EDIT]
just adding my calculations to get start and end rows for selected week:

If SelectedWeek = 1 Then
    StartRow = 3
    EndRow = 9
    Else
        StartRow = ((SelectedWeek * 6) + 3) - 6
        EndRow = (SelectedWeek * 6) + 3
End If

all i want to update in the above code is 1 and 10 row references.
each chart has different source columns, but the same source rows so i dont want to hard code the range for each chart.

TIA

[Br1an_g]

ok, so couldnt figure it out, so i took another approach.

I set the graphs source to incorporate the complete range of data.

I then created a macro to hide the rows that were outwith the week that had been selected.

this has the effect of updating the graphs which only show visible data.

'assign start and end rows for selected week
StartRow = (SelectedWeek * 8) - (2 + SelectedWeek)
EndRow = StartRow + 8
 
'update row source for charts
    'first select source sheets
    Sheets("Sheet1").Activate
    ActiveSheet.Cells.Select
    Selection.EntireRow.Hidden = False
    
    'hide all but current weekly rows
    ActiveSheet.Range("4:" & StartRow & "," & EndRow & ":368").Select
    Selection.EntireRow.Hidden = True
    
    Sheets("Sheet2").Activate
    ActiveSheet.Cells.Select
    Selection.EntireRow.Hidden = False
    
    'hide all but current weekly rows
    ActiveSheet.Range("4:" & StartRow & "," & EndRow & ":368").Select
    Selection.EntireRow.Hidden = True
    
Application.ScreenUpdating = True
 
'activate graphs sheet
Worksheets("Graphs").Activate

bit messy but im still working on it
[EDIT] updated code