Creating a Macro time stamp

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

storm - 24 Sep 2007 13:06 GMT

I am trying to create a very simple macro.

(1) enter current time i.e. @now()
(2) copy this time and paste special as value (to fix the current time
as a time stamp)

Every time I create the macro and run it, it creates the @now() time
value BUT does not fix it as a value.

The following is the generated code:

ActiveCell.FormulaR1C1 = "=NOW()"
Range("D32").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("E32").Select
End Sub

Reply to this Message

JE McGimpsey - 24 Sep 2007 13:31 GMT

> I am trying to create a very simple macro.
>
[quoted text clipped - 15 lines]
> Range("E32").Select
> End Sub

First, you do know that you can enter the time (rounded to the minute)
by typing CMD-; right?

Second, the reason your code isn't working is that you select a
different cell between entering the formula and copying - while you're
entering the formula in the active cell, you're always copying and
pasting the value in cell D32.

Third, there's no reason to use the XL function when you can use VBA's
Time method (to enter just the time) or Now method (which, like the XL
function, includes the date). Better:

With ActiveCell
.Value = Time ' or Now
.NumberFormat = "hh:mm"
End With

Reply to this Message

storm - 24 Sep 2007 16:58 GMT

> In article <1190635593.409690.79...@r29g2000hsg.googlegroups.com>,
>
[quoted text clipped - 34 lines]
> .NumberFormat = "hh:mm"
> End With

I am but a poor novice working on an iMac.

CMD- gives me the date
CMD; gives me the time
CMD-; gives me a literal I can not use

On the Macro which I created using "record new Macro", (1) those steps
gave me a time stamp but activating the Macro did not, (2) I went back
and edited the Macro to change E32 to D32 as you suggested but that
gave the same result as before.

I assume "XL function" means Macro. I don't know what VBA is, nor
understand what you mean by using the "Time method".

I guess you must think I am stupid - but all I want to do is have a
simple way of time stamping a cell.

And I still have no idea of how to do that.

Reply to this Message

JE McGimpsey - 24 Sep 2007 17:28 GMT

> I am but a poor novice working on an iMac.
>
> CMD- gives me the date
> CMD; gives me the time
> CMD-; gives me a literal I can not use

One convention for writing key combinations is to use a hyphen to
separate the keys. So I meant CMD-; to be the semicolon key while
holding down CMD.

> On the Macro which I created using "record new Macro", (1) those steps
> gave me a time stamp but activating the Macro did not, (2) I went back
> and edited the Macro to change E32 to D32 as you suggested but that
> gave the same result as before.

I wasn't suggesting changing E32 to D32. I was saying that the selection
was the cause of your problem - if you didn't want to use my
alternative, you should take out both the

Range("D32").Select

and the

Range("E32").Select

statements.

> I assume "XL function" means Macro.

No it means Excel Function. For instance =NOW().

> I don't know what VBA is, nor
> understand what you mean by using the "Time method".

Visual Basic for Applications (VBA) is the language that macros are
written or recorded in. Your recorded macro is an example of VBA.

VERY roughly - commands in VBA that return values are called methods.

So what I was suggesting is that instead of inserting the Excel Function
NOW() into the cell, then copying and paste special-ing, that it was
easier to insert the value from VBA's built-in Time function/method.

That doesn't mean your approach was wrong (except for changing the
selected cell in the middle). There are almost always multiple ways to
accomplish a process in XL & VBA.

> I guess you must think I am stupid

Nope - just ignorant about VBA. Ignorant's a lot better than stupid -
I'm ignorant about nearly everything. VBA just happens to be one of the
things I know something about.

> but all I want to do is have a
> simple way of time stamping a cell.
>
> And I still have no idea of how to do that.

Easiest: Type CMD and ; keys

If you want to record seconds, or a particular format, paste this macro
into a regular code module in your Personal Macro Workbook:

Public Sub TimeStamp()
With ActiveCell
.Value = Time
.NumberFormat = "[hh]:mm:ss"
End With
End Sub

(or substitute your favorite time format).

For more on where to put your code, see

http://www.mcgimpsey.com/excel/modules.html

and

http://www.mvps.org/dmcritchie/excel/getstarted.htm

(gives examples for WinXL - substitute MacXL's "Personal Macro Workbook"
for the references to WinXL's "Personal.xls")

Reply to this Message

CyberTaz - 24 Sep 2007 18:06 GMT

Hi -

I'm just butting in for clarification here because your last message seems
to contradict itself:)

On the one hand you confirm that John's suggestion works:

> CMD; gives me the time

Then you end with the statements:

> - but all I want to do is have a
> simple way of time stamping a cell.
>
> And I still have no idea of how to do that.

By "time stamping" a cell, do you mean having the current time entered into
the cell or do you mean having some way of *tracking* the time at which
canges to the cell were made? The latter is a whole different kettle of
fish. [BTW, the CMD-; is one means of denoting Command Key along with the ;
key, also frequently written as CMD+;]

Signature

Regards |:>)
Bob Jones
[MVP] Office:Mac

Reply to this Message

storm - 24 Sep 2007 18:30 GMT

> Hi -
>
[quoted text clipped - 21 lines]
> Bob Jones
> [MVP] Office:Mac

Mr. Jones:

CMD- gives me the date without the time
CMD; gives me the time without the date
CMD-; gives me a literal I cannot do math on

Mr. McGimpsey:

I went back and edited out the cell references and that works:

ActiveCell.FormulaR1C1 = "=NOW()"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End Sub

Thanks to both of you for your help,

Bill Garnett
Midlothian, VA

Reply to this Message

Mac Forum / Applications / Excel / September 2007

Creating a Macro time stamp