Using macro to set chart title with a variable month
 

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:

-------
ActiveChart.ChartTitle.Select
Selection.Characters.Text = _
"Sales Performance (YTD October 2007)" & Chr(10) & "2006 Actual vs
2007 Budget vs 2007 Actual"
-------

I've tried replicating the above formula in the same manner, but this just
enters the whole formula as-is, without calling the MONTH and YEAR ranges. I
assume if I can fix the first problem, I can fix the second in the same way?

Using macro to set chart title with a variable month
 

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

Using macro to set chart title with a variable month
 

Found a suitable answer he

http://www.microsoft.com/office/comm...2fsettings.xml