I've spent 2 weeks banging my head against the wall, and coming up with "workarounds" to every roadblock rather than solutions. I've finally reached an impasse.

I'm using Excel 2000, Windows 7, and writing macros with vba for a Line chart. My workbook has 2 worksheets and 1 Chart Sheet - but I plan to add a sheet for each new year moving forward. A worksheet is active when the macro sub runs. The worksheets are named using a 4-digit year.

I am clueless as to why the following lines of code are not the same, but the first set works fine (but doesn't help) and the second set throws a '1004' exception.

Set-up:
Dim MyYear as String
Dim RangeStr as String

MyYear = ActiveSheet.Name 'for example, "2012"

The following 2 lines work fine
RangeStr = "=('2012'!R10C2:R10C27,'2012'!R34C2:R34C28)"
Charts("Chart").SeriesCollection(1).Values = RangeStr

The following two lines set RangeStr to what appears to be the exact same text, but the line that sets .Values barfs with a '1004' error. (Please forgive the fact that I did NOT clean up the line of code that overruns the margins for this posting.)
RangeStr = "=('" & MyYear & "'!R10C2:R10C27,'" & MyYear & "'!R34C2:R34C28"
Charts("Chart").SeriesCollection(1).Values = RangeStr

Can anyone tell me why what appears to be the same string apparently isn't? And having learned that, how DO I build a string from variables to set the .Values parameter?

What I've tried:
1 - I tried doing the 2nd set of code lines first, to no avail.
2 - I tried changing the chart type before making the assignment

TIA for any help you can render.