NOW keeps updating previous entries

[thb63]

I've set up a macro to enter the NOW function to have the date and time. I want this so it shows down to the seconds. The steps are here:

Go to an empty cell<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Click Tools, Macro, Record New Macro . . .<o></o>
Give a proper name such as Insert_Date<o></o>
Hold Shift and type a “D” into the Shortcut Key box<o></o>
Select Store In Personal Macro Workbook (unless they always use the same workbook then you can save it to that file, then whomever uses the file can use the macro)<o></o>
Click OK<o></o>
Type “=now()”<o></o>
Click the green checkmark next to the formula bar<o></o>
Copy and paste special values<o></o>
Click the Stop button on the macro toolbar

The problem is it keeps changing the previous entries to the last date/time entered using the macro. Any suggestions? Using CTRL SHIFT : doesn't give me down to the seconds like I need.

Thanks,
Tom

 

[Rekd]

I've set up a macro to enter the NOW function to have the date and time. I want this so it shows down to the seconds. The steps are here:

Go to an empty cell<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Click Tools, Macro, Record New Macro . . .<o></o>
Give a proper name such as Insert_Date<o></o>
Hold Shift and type a “D” into the Shortcut Key box<o></o>
Select Store In Personal Macro Workbook (unless they always use the same workbook then you can save it to that file, then whomever uses the file can use the macro)<o></o>
Click OK<o></o>
Type “=now()”<o></o>
Click the green checkmark next to the formula bar<o></o>
Copy and paste special values<o></o>
Click the Stop button on the macro toolbar

The problem is it keeps changing the previous entries to the last date/time entered using the macro. Any suggestions? Using CTRL SHIFT : doesn't give me down to the seconds like I need.

Thanks,
Tom

Now you have to copy and paste the value into the cell instead of the formula.

I use this, it's attached to a button so I select the cell I want the date in and click the button. (Not formatted for seconds, but you can change that)

Sub CurrDateTime()
' CurrDateTime
    ActiveCell.FormulaR1C1 = "=NOW()"
    Range(ActiveCell.Address).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

 

[meldoc]

Hello

Or just.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> datetime()<br>  ActiveCell = <SPAN style="color:#00007F">Date</SPAN> & " " & Time<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[JackDanIce]

A minor modification of Rekd's code might be:

Sub CurrDateTime()
' CurrDateTime
With Activecell
  .Formula = "=NOW()"
  .Value = .Value
  .NumberFormat = "DD/MM/YYYY HH:SS"
End With
End Sub

You can then attach this code to a button and adjust the line in red to give the format style that you want. I'd suggest avoiding using the .Select in VBA code as it can slow your macro down, cause the screen to flicker and creates other problems too (search the other entires on this board for a better explanation if you want)

 

[Rekd]

Hello

Or just.

Sub datetime()
ActiveCell = Date & " " & Time
End Sub

Nobody likes a smart-***.

Just kidding. Thanks, that's much more efficient.

 

[meldoc]

Hello Rekd

Thanks.

But be aware that it returns text to display the seconds, as requested.
If you wanted to return, a proper date, use the below and format as required.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> datetime()<br>  ActiveCell = <SPAN style="color:#00007F">Date</SPAN> + Time<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>