How to add todays date (static) to the current active cell using m

[Guest]

Hello,

I have a worksheet that I'd like to apply the current date and time
(adjacent cells) in the next blank row auotmatically when the worksheet is
opened. i.e. if row 10 is blank on open, then A10 will have the date applied
and A11 the time. Both values must remain static.

I've started by trying to write a macro that will insert the current date
value into the active cell. See code below:

Dim james_stamp As Date
Dim james_range As Range
james_stamp = Now()
james_range = ActiveCell.Range
Range("james_range").Value = james_stamp

I'm having problems capturing the active cell and then using this value
within the 'Range' function. My next challenge was replacing the active cell
value with the 'next blank cell'. Then I'd need to run this macro on opening
the worksheet.

Can anyone push me in the right direction?

Many thanks,

JJ

 

[Dave Peterson]

Dim DestCell as range
with worksheets("logsheet")
set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with
destcell.value = now

 

[Guest]

Something like the following should do it. Note that this should be saved in
the 'ThisWorkbook' module of the sheet in qusetion and it will run
automatically when the workbook is opened.

Private Sub Workbook_Open()
ActiveCell.Offset(0, 1) = Now()
End Sub

HTH,

TK

 

[Guest]

Oops, I think I misread your post...better get new reading glasses.

The following change should take care of finding the first blank row in
column A of "Sheet1". I also changed to create two cells, one for the date
and on for the time:

Private Sub Workbook_Open()
With Sheets("Sheet1")
If .Range("A1") = "" Then
.Range("A1") = Format(Now(), "MM/DD/YY")
.Range("A2") = Format(Now(), "h:mm:ss")
Else
.Range("A1").End(xlDown).Offset(1, 0) = Format(Now(), "MM/DD/YY")
.Range("A1").End(xlDown).Offset(1, 0) = Format(Now(), "h:mm:ss")
End If
End With
End Sub

You can modify the date/time formats as needed with any date or time format
that Excel recognizes.

HTH,

TK

 

[Guest]

Fantastic - thanks guys for this, I'll give it a try.
Jim

 

[Guest]

OK

Cells A2 and B2 update on initial run but I cannot get the macro to
increment the subsequent rows (i.e, A3:B3 - A4:B4).

No error is occuring, it's just not updating the sheet.

Here is my code

Private Sub Workbook_Open()
With Sheets("Sheet1")
If .Range("A2") = "" Then
.Range("A2") = Format(Now(), "MM/DD/YY")
.Range("B2") = Format(Now(), "h:mm:ss")
Else
.Range("A2").End(xlDown).Offset(0, 1) = Format(Now(), "MM/DD/YY")
.Range("B2").End(xlDown).Offset(0, 1) = Format(Now(), "h:mm:ss")
End If
End With
End Sub

Any ideas?

Thanks