Actively update macro reference?

[Jennifer Bastin]

Hi,

I've just been set the task of summarising a weekly report into on
workbook. The report is always named "Reconciliation From 150304
(date changes weekly). It contains 5 different worksheets which alway
follow the same layout but changes info contained according to date.

In the summary we currently reference each cell manually at the star
of each week but have found that this is subject to manual formul
errors. eg

='N:\Reconciliation\March 2004\[Reconciliation From 150304.xls]SHEE
ONE'!$G$82

I have tried to create a macro that will automate the reproduction o
the formula using the appropriate date but have found that the macr
will only work with the date that it was recorded with.

Is it possible to record a macro that will update when a new date i
entered and then fill in a summary table?

Any help would be greatly appreciated!!

Jennife

 

[Bernie Deitrick]

Jennifer,

The basic technique would be to do something like this, which will put your
formula into cell A1: unfortunately, I'm not sure which date you want to
update - the 150304...

Range("A1").Formula = "='N:\Reconciliation\March 2004\[Reconciliation From "
& _
Format(now(),"ddmmyy") & ".xls]SHEET ONE'!$G$82"

or the March 2004...

Range("A1").Formula = "='N:\Reconciliation\" & _
Format(Now(), "mmmm yyyy") & _
"\[Reconciliation From 150304.xls]SHEET ONE'!$G$82"

or (what I suspect most strongly) both....

Range("A1").Formula = "='N:\Reconciliation\" & _
Format(Now(), "mmmm yyyy") & "\[Reconciliation From " & _
Format(Now(), "ddmmyy") & ".xls]SHEET ONE'!$G$82"

HTH,
Bernie
MS Excel MVP

 

[Jennifer Bastin]

Thanks Bernie, sounds just the job! :