macro

[Guest]

How can I set an excel spreadsheet to automatically insert a current date and
save that date when the workbook is closed. The next time the workbook is
open on a different day it inserts the current date two rows from the last
row that contains data. For example:

Date: 05/09/06 – Automatic insert current date when Workbook is open
Tom Hanks 8hr $12.00 Hr.
Paul Thomas 10hr $11.00 Hr.

Close workbook….date is saved automatically
Next day….open workbook and Excel automatically inserts the current date two
rolls down from last data entered. For example:

Date: 05/09/06
Tom Hanks 8hr $12.00 Hr.
Paul Thomas 10hr $11.00 Hr.


Date: 05/10/06
Tom Hanks 7hr $12.00 Hr.
Paul Thomas 8hr $11.00 Hr.
Sue Smith 8hr $12.00 Hr.

 

[mrice]

Sounds like you want a couple of macros linked to the
Workbook_BeforeClose and Workbook_Open.

The line would be something like...

Cells(65536,1).end(xlup).offset(0,2) = Now()

 

[Guest]

mrice,
Thank you for your help. I have two questions.....Isn’t there a way to have
the workbook run a macro automatically when you open the workbook? (that way
it would eliminate manually running a macro) If so, can’t I just have the
date coded in the macro like the short cut ctrl + : That way I could just
save without running a close macro.

 

[Guest]

This should work. If you don't know where to put it, press F11 with Excel
active. Then in the Visual Basic Editor click on the workbook you wish to
add this macro into and click on the "ThisWorkbook" file. Then just cut and
paste this into it. Unfortunately it doesn't acount for if the workbook is
opened multiple times in one day. Someone else may provide some refining to
the code to make it look a lot better (Im new to VB). If you want it to only
add a new entry on a new day let me know and I'll see if I can write it up
quickly.

Be sure to adjust "Sheet4" to your worksheet name and have the quotes in
there. Same thing with "A" if you wish for the macro to search for the last
row and write on a different column.

Sub Workbook_Open()
Dim i As Integer

i = 1
Do While (Worksheets("Sheet4").Range("A" & i).Value <> "") Or
(Worksheets("Sheet4").Range("A" & i + 1).Value <> "")
i = i + 1
Loop
i = i + 1

Worksheets("Sheet4").Range("A" & i).Value = "Date: " & Date

End Sub

mrice,
Thank you for your help. I have two questions.....Isn’t there a way to have
the workbook run a macro automatically when you open the workbook? (that way
it would eliminate manually running a macro) If so, can’t I just have the
date coded in the macro like the short cut ctrl + : That way I could just
save without running a close macro.

 

[mrice]

You could also use a macro titled

AUTO_OPEN

to acheive the same thing.