VBA Formula Help

[Exceller]

I recorded/tweaked the macro below to add today's date to a cell, and then
copy/paste-values over it to "freeze it".

In order to record the macro I had to select the cell I wanted to write the
formula to, and then again to copy/paste-values. How can I change the code
of the macro to "point" to the input cell to both write the "Today()" formula
and copy/paste-values without having to select it? You'll see below what I'm
talking about:

Range("D4").Select
ActiveCell.FormulaR1C1 = "=TODAY()"...........to input the formula

and

Range("D4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False................to do the copy/paste-values

Below is the full macro. Thanks.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/14/2009

'
Application.ScreenUpdating = False
Range("D4").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("D4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[Chip Pearson]

Just use

Range("D4").Value = Format(Now,"hh:mm")

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Patrick Molloy]

Range("D4")=format$(Date,"dd/mm/yyyy")

 

[JLGWhiz]

Since you are using VBA, why not just:

Sub dk()
Range("D4") .Value = Date
End Sub

 

[Exceller]

Thanks, All for your valuable help!
Regards.

 

[Dave Peterson]

I'd use:

with worksheets("Somesheetnamehere").range("D4")
.numberformat = "mm/dd/yyyy"
.value = date
end with