Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Actively update macro reference?
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Relative reference won't update | Excel Discussion (Misc queries) | |||
Update End of Col or End of Row Reference | Excel Discussion (Misc queries) | |||
take spreadsheet to laptop and actively check boxes with pen | Excel Discussion (Misc queries) | |||
Using Excel as an actively linked workbook in Access | Excel Worksheet Functions | |||
Problem With Reference Update | Excel Worksheet Functions |