updating row source for charts in VBA [RESOLVED]

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]

VB Code:

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:

VB Code:

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

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.

VB Code:

'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