macro that will insert today's date

  • Thread starter Thread starter shirley
  • Start date Start date
S

shirley

I want a macro (that I assign to a toolbar button) that
will insert today's date, but will not update the date
when I open the program in a few day's time.
I've tried:-
InsertDate Sub()
ActiveCell.FormulaRICI = "=NOW()"
SctiveCell.Offset(0, 1).Range("A1").Select
End Sub
I have also tried replacing the "NOW" with "TODAY"
and "DATE".
 
Hi Shirley

ActiveCell = Date

--
XL2002
Regards

William

(e-mail address removed)

| I want a macro (that I assign to a toolbar button) that
| will insert today's date, but will not update the date
| when I open the program in a few day's time.
| I've tried:-
| InsertDate Sub()
| ActiveCell.FormulaRICI = "=NOW()"
| SctiveCell.Offset(0, 1).Range("A1").Select
| End Sub
| I have also tried replacing the "NOW" with "TODAY"
| and "DATE".
 
You could add at the end of the Macro Copy and Paste As Value the date--that
way it won't update.
There might be better ways to do it....
 
Shirley,

I don't know if the code you posted is real, or typed in, but there is a
significant error there

The code is

Activecell.FormulaR1C1 = Date (or Now if you want time as well

Note not FormulaRICI, and not "=DATE()". The first is invalid syntax, the
second inputs a formula that will update, not what you want.

Although that will work, formula is not necessary, and you could simply use

Activecell.Value = Date

Note also that TODAY() is a worksheet function, not VBA.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
-----Original Message-----
I want a macro (that I assign to a toolbar button) that
will insert today's date, but will not update the date
when I open the program in a few day's time.
I've tried:-
InsertDate Sub()
ActiveCell.FormulaRICI = "=NOW()"
SctiveCell.Offset(0, 1).Range("A1").Select
End Sub
I have also tried replacing the "NOW" with "TODAY"
and "DATE".
.
Click to expand...
 
You need to copy the value of the formula over the date

ActiveCell.FormulaR1C1 = "=TODAY()"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
 
While this will certainly work, there's an easier way:

Public Sub InsertDate
With ActiveCell
.Value = Date
.NumberFormat = "dd mmm yyyy"
End With
End Sub
 
Sub NOWDATE()
ActiveCell.Value = Format(Date, "dd-mmm-yy")
End Sub

Gord Dibben Excel MVP
 
Back
Top