Using macro to set chart title with a variable month

[baldmosher]

I have named two cells as range MONTH and YEAR respectively, so that I can
update my report automatically each month.


My macro:

-------
Range("C1").Select
ActiveCell.FormulaR1C1 = _
"=""Sales Performance (YTD ""&MONTH&"" ""&YEAR&"")"""
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=&YEAR-1&"" Actual vs ""&YEAR&"" Budget vs ""&YEAR&"" Actual"""
-------

This labels the datasheet (cell C1) as the following formula:

="Sales Performance (YTD "&MONTH&" "&YEAR&")"

Which does the trick. However, I want the macro to set the datasheet title
not as a formula, but as text, using MONTH and YEAR as fixed constants, so
that it reads:

Sales Performance (YTD October 2007)

I've tried amending the fomula as follows:

-------
ActiveCell.FormulaR1C1 = _
"Budgeted Trade Lane Performance (YTD " & Month & " " & Year & ")"
-------

but as soon as I type this it conerts MONTH and YEAR to Month and Year, and
gives an error when I try to run the macro. What I believe I need to do is
to Dim Month as MONTH and Year as YEAR at the start of the macro but I'm not
sure how to do this?



I also have a second problem. I need to do a similar thing to the titles of
Chart1, Chart2 and Chart3. But I can't even figure out how to get the macro
to label the chart using a formula, as above. The macro currently reads:

 

[baldmosher]

Bit more info, have already tried adding the following:

Const Month As String = "MONTH"
Const Year As String = "YEAR"

which changes the title to ....YTD MONTH YEAR

and

Const Month As String = MONTH
Const Year As String = YEAR

which fails

 

[baldmosher]

Found a suitable answer here:

http://www.microsoft.com/office/com...osoft.com/office/community/en-us/settings.xml