Actively update macro reference?
 

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

--
Message posted from http://www.ExcelForum.com

Actively update macro reference?
 

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 " wrote in
message ...
Hi,

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

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

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

I have tried to create a macro that will automate the reproduction of
the formula using the appropriate date but have found that the macro
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 is
entered and then fill in a summary table?

Any help would be greatly appreciated!!

Jennifer


---
Message posted from http://www.ExcelForum.com/

Actively update macro reference?
 

Thanks Bernie, sounds just the job! :

--
Message posted from http://www.ExcelForum.com