Using a string to make a sheet reference question

[cfdh_edmundo]

Hi,

I'm trying to perform a sheet reference (for an axis on a chart) in VBA but it seems to keep going wrong.

When I hard-code the sheet path in it works, e.g.

ActiveChart.SeriesCollection(1).XValues = "='01.12.09'!R4C1:R45C1"

This picks up the range A4:A45 from the sheet named 01.12.09 and uses it as the x axis values.


However when I try and make this more dynamic, by replacing the 01.12.09 with a string which has the sheet name (dateLookingAt is used to generate the name of the sheet) it seems to have problems:

ActiveChart.SeriesCollection(1).XValues = ""=dateLookingAt&"!R4C1:R45C1"

I think I've messed up the "" and/or ''. Does anyone know how to solve this?

Many thanks,

Chris

 

[pgc01]

Hi Chris

Try:

ActiveChart.SeriesCollection(1).XValues = "='" & dateLookingAt & "'!R4C1:R45C1"

 

[cfdh_edmundo]

brilliant! thanks very much, works perfectly.