macro to change worksheet reference in charts

[maximus315]

I am trying to write a macro that changes just the worksheet name in a chart that is already set up. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Situation:<o></o>
I have a worksheet setup with multiple charts. All the charts reference ranges in a template worksheet. A new worksheet is created from the template for each new batch loaded in “YTDDATA” worksheet. The ranges in the new worksheets will all be the same so I just want to change the referenced worksheet name in the charts to update to the latest batch. All the charts have different ranges. I have <o></o>
<o> </o>
Step one -- to locate the last batch added in YTDDATA – this works<o></o>
Step two – open the worksheet with the charts “Operator Trends” – this works <o></o>
Step three -- change worksheet name referenced in charts -- this is where I am having trouble.
<o> </o>
Help would be greatly appreciated. <o></o>
<o> </o>
<o> </o>
<o> </o>
code<o></o>
<o> </o>
<o> </o>
<o> </o>
<o> </o>
Sub Chartupdatenew()<o></o>
'<o></o>
'<o></o>
Dim iCurrentRow As Integer<o></o>
Dim i As Integer<o></o>
Dim c As Integer<o></o>
Dim sSheetName As String<o></o>
<o></o>
'Get last row with a batch number<o></o>
Sheets("YTDDATA").Select<o></o>
<o></o>
Dim bEmptyCellFound As Boolean<o></o>
<o></o>
'set Variables<o></o>
i = 7 'start at row 7<o></o>
bEmptyCellFound = False<o></o>
<o></o>
Do Until bEmptyCellFound = True<o></o>
If (Sheets("YTDDATA").Cells(i, 8).Value < 1) Then<o></o>
iCurrentRow = i 'Row information for later use<o></o>
bEmptyCellFound = True ' set loop exit flag<o></o>
c = i - 1<o></o>
End If<o></o>
i = i + 1 'step to next row<o></o>
<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on">Loop</st1lace><o></o>
<o></o>
'set sSheetName to batch worksheet name<o></o>
<o> </o>
sSheetName = Sheets("YTDDATA").Cells(c, 8).Value<o></o>
<o></o>
'set worksheet to chart worksheet<o></o>
<o></o>
Sheets("Operator Trends").Select<o></o>
Sheets("Operator Trends").Activate<o></o>

'change worksheet name referenced in charts<o></o>
<o> </o>
ActiveSheet.ChartObjects("Chart 4").Activate<o></o>
ActiveChart.SeriesCollection(1).XValues = "=ssheetname!R72C1:R113C1"<o></o>
ActiveChart.SeriesCollection(1).Values = "=sSheetname!R72C57:R113C57"<o></o>
<o> </o>
End Sub

 

[maximus315]

never mind i figured it out
just needed to change the reference code a little

sSheetName = "'" & Sheets("YTDDATA").Cells(c, 8).Value
and then change
ActiveChart.SeriesCollection(1).XValues = "=" & sSheetName & "'!R72C1:R113C1"