I have a workbook that I use month to month named "Report
MTH YY" This month the report is named "Report Jul 04".
Next month the report will be renamed "Report Aug 04",
then "Report Sep 04" etc.

I have formulas that reference last months report. As
simple as " ='[REPORT Jun 04.xls]Sheet 1'!A1". Next
month in "Report Aug 04" I want the formula to reference
the Jul report i.e. ='[REPORT Jul 04.xls]Sheet 1'!A1" .

I am looking for a way to automate the process rather
than manually using "Find & Replace", but I do not know
how to get the month names to change automatically.

More specifically, I am looking for a way to create a
macro using "Find & Replace" that will find and replace
the month name. So I have;

Cells.Select
Selection.Replace What:="Month(Now()-1",
Replacement:="Month (Now()", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Obviously it does not work. I need to know how to create
the "What:=" and the "Replacement:=" to -
What:= "last month (Jun)"
Replacement:= this month (Jul)

Thanks to Tom Ogilvy for the solution using "Indirect",
however the workbook that the data is generated from is
never open, but it is always in the same directory.


Thanks for any additional help.