AUTO-CHANGING THE DATE
 

I HAVE MONTHLY FINANCE REPORT WHICH HAS DATA THAT CALLING UP FROM WORKBOOKS CREATED DAILY THAT HAVE NAMES AS THE DATE CHANGES(ie FR291203(TODAY'S DATE)). EACH DAY I SHOULD COPY YESTERDAY'S FORMULA & PASTE IT ON 2 ROW BELOW OF THE YESTERDAY'S TABLE AND REPLACE(EDIT-REPLACE) A PART OF FORMULA TO TODAY'S DATE (ie =FR291203 TO =FR301203). IS THERE ANY EASY WAY TO AUTOMATE IT WITH A MACRO?
NEED HELP ;-)
THNX

AUTO-CHANGING THE DATE
 

please don't SHOUT!

Yes, this is what code is for.

You need to set a variable to hold the workbook name

dim sWB as string
sWB = "c:\temp\FR" & fromat(date,"ddmmyy") & ".xls"
''' note the full path !!
'' next set a variable to point to the active worksheet
'' and to the book you want to open

dim ThisWS as worksheet
DIM FRWB as workbook


set ThisWS = ThisWorkbook.Worksheets("results")

set FRWB = Workbooks.Open(sWB)

''' code here to copy the data


''close the FRdate workbook without saving !
FRWB.Close False
SET FRWB = NOTHING



this is basically th ewrapper top set the FR workbook
name, open it, then close it.
Decide how you want the data to be copied.
You might do it like this.
Open the FRBook. Start the macro recorder, copy the data
stop the recorder and close the FR book. Examine the
recorded code

Let us know the next steps once you've tried this
Patrick Molloy
Microsoft Excel MVP







-----Original Message-----
I HAVE MONTHLY FINANCE REPORT WHICH HAS DATA THAT
CALLING UP FROM WORKBOOKS CREATED DAILY THAT HAVE NAMES
AS THE DATE CHANGES(ie FR291203(TODAY'S DATE)). EACH DAY
I SHOULD COPY YESTERDAY'S FORMULA & PASTE IT ON 2 ROW
BELOW OF THE YESTERDAY'S TABLE AND REPLACE(EDIT-REPLACE)
A PART OF FORMULA TO TODAY'S DATE (ie =FR291203 TO
=FR301203). IS THERE ANY EASY WAY TO AUTOMATE IT WITH A
MACRO?
NEED HELP ;-)
THNX
.

AUTO-CHANGING THE DATE
 

Patrick Molloy wrote

sWB = "c:\temp\FR" & fromat(date,"ddmmyy") & ".xls"


assume you meant format instead of fromat, lest a lurker or OP copy & paste
and wonder why it doesn't work.

--
David