Advanced Referencing Question

[Chad]

So lets say I one file named January Sales.xls and I have a another file
named Yearly Sales.xls and I have formulas that reference cells in January
Sales.xls and post them in Yearly Sales.xls. However once I start entering
data for February sales I enter it into the January Sales.xls and just change
the name to February Sales.xls. Is there a macro I can run that will change
the name of one month and replace it with another based on what the current
month is so that if it was Feb 3rd and I ran the macro it would change all
formulas linking to January Sales.xls to linking February Sales.xls?

And yes I know it would be easier to put this information in one workbook or
at least different workbooks but this is not the exact situation just one
that will simply my problem into easier understandable terms.

 

[Sheeloo]

If you have both files open and save the Jan file with a different name then
all the links in Yearly Sales will get updated to the new name.

Of course you can write a macro to update all the references.

 

[Shane Devenshire]

Hi,

If you don't have both files open, then when you open the Yearly Sales.xls
file, don't update. Choose Edit, Links, select the January Sales.xls file
from the list and choose Change Source, pick the February Sales.xls file and
all the formulas will adjust.

 

[Chad]

If you have both files open and save the Jan file with a different name then
all the links in Yearly Sales will get updated to the new name.

Of course you can write a macro to update all the references.

Thanks but the question is can I write a macro to auto update part of a file
name to the current month.

 

[Chad]

Hi,

If you don't have both files open, then when you open the Yearly Sales.xls
file, don't update. Choose Edit, Links, select the January Sales.xls file
from the list and choose Change Source, pick the February Sales.xls file and
all the formulas will adjust.

Again Thanks for the input but I am looking for a MACRO and I repeat MACRO
that would auto search for a month's name and then replace that with the
current month's name even if it is part of a file name.

 

[Shane Devenshire]

Hi,

The manual command is Ctrl+H, ...

You can turn on the recorder and record the steps and then look at the code.
If you need help modifying the code that is recorded post it back here.

The problem is code is very specific and you have given us very little
detail. How are you spelling the month names Jan, J09, January, Jan-08...
How do you want to trigger the macro, when the workbook opens, manually via a
shortcut key, or a toolbar button. What happens when going from Dec --> Jan?
Are there formulas with links on all sheets, do you have chart sheets and
spreadsheet? Are there links in charts, in the defined names? What do you
want to do if someone tries to run the macro twice, how will the code know if
it is a new month or not.

 

[Chad]

Hi,

The manual command is Ctrl+H, ...

You can turn on the recorder and record the steps and then look at the code.
If you need help modifying the code that is recorded post it back here.

The problem is code is very specific and you have given us very little
detail. How are you spelling the month names Jan, J09, January, Jan-08...
How do you want to trigger the macro, when the workbook opens, manually via a
shortcut key, or a toolbar button. What happens when going from Dec --> Jan?
Are there formulas with links on all sheets, do you have chart sheets and
spreadsheet? Are there links in charts, in the defined names? What do you
want to do if someone tries to run the macro twice, how will the code know if
it is a new month or not.
Cheers,
Shane Devenshire

The month is always spelled out as a part of the file name such as June
Sale.xls. I would like it to do this when you open the worksheet. From Dec it
should roll over to Jan since years are not listed. It is not charts just
numbers. The code should check for a month in the formulas then check if that
month is current month if so then nothing if not the current month then
replace with current month. With that last part if the macro was ran twice
nothing would happen.