Insert date in macro

[George Gee]

Happy new year to all!

I recorded this macro some time ago, the cell A5 is selected at the end of
the macro,
ready for the user to insert today's date.
How do I edit the macro to insert today's date into cell A5, when it is run.

I do not want to use =TODAY() as this would change the date, daily,
(does that make sense)?

Many thanks

--
George Gee
--

[Andy Brown]

> How do I edit the macro to insert today's date into cell A5, when it is
run.

Add at end:

Range("Sheet1!A5") = Date

Rgds,
Andy

[Harald Staff]

Hi George

Sure. It is very simple:

Range("A5").Value = Date

You can also use Now (for date and time) and Time (for time without date)

HTH. Best wishes Harald


"George Gee" <[email protected]> skrev i melding
news:[email protected]...
> Happy new year to all!
>
> I recorded this macro some time ago, the cell A5 is selected at the end of
> the macro,
> ready for the user to insert today's date.
> How do I edit the macro to insert today's date into cell A5, when it is
run.
>
> I do not want to use =TODAY() as this would change the date, daily,
> (does that make sense)?
>
> Many thanks
>
> --
> George Gee
> --
>
>

[George Gee]

I suppose it would help if I included the macro!

Range("A5:G5").Select
Selection.Insert Shift:=xlDown
Range("B3:F3").Select
Selection.Copy
Range("B5:F5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("B3:F3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("B5").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF(B5>B6,TRUE)"
Selection.FormatConditions(1).Font.ColorIndex = 5
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF(B5<B6,TRUE)"
Selection.FormatConditions(2).Font.ColorIndex = 3
Selection.Copy
Range("B5:F5").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Application.CutCopyMode = False
Range("A5").Select
End Sub

*George Gee* has posted this message:

> Happy new year to all!
>
> I recorded this macro some time ago, the cell A5 is selected at the
> end of the macro,
> ready for the user to insert today's date.
> How do I edit the macro to insert today's date into cell A5, when it
> is run.
>
> I do not want to use =TODAY() as this would change the date, daily,
> (does that make sense)?
>
> Many thanks


>
> --
> George Gee
> --

[George Gee]

Andy, Harald.

Thank you very much, just what I wanted!

George Gee



*Harald Staff* has posted this message:

> Hi George
>
> Sure. It is very simple:
>
> Range("A5").Value = Date
>
> You can also use Now (for date and time) and Time (for time without
> date)
>
> HTH. Best wishes Harald
>
>
> "George Gee" <[email protected]> skrev i melding
> news:[email protected]...
>> Happy new year to all!
>>
>> I recorded this macro some time ago, the cell A5 is selected at the
>> end of the macro,
>> ready for the user to insert today's date.
>> How do I edit the macro to insert today's date into cell A5, when it
>> is run.
>>
>> I do not want to use =TODAY() as this would change the date, daily,
>> (does that make sense)?
>>
>> Many thanks
>>
>> --
>> George Gee
>> --

[Andy Brown]

"George Gee" <[email protected]> wrote in message
news:%[email protected]...
> I suppose it would help if I included the macro!
>
> Range("A5:G5").Select
> Selection.Insert Shift:=xlDown
> Range("B3:F3").Select
> Selection.Copy

For efficiency, you should take out as much physical selection as poss.
Forex, replace the last 2 lines with:

Range("B3:F3").Copy

Rgds,
Andy

[George Gee]

Andy

Thanks for that, have replaced as you have indicated, and it does run more
smoothly!

As I said, this macro was *recorded*, something even *I* can do!
Editing is another matter!

Thanks again.

George Gee


*Andy Brown* has posted this message:

> "George Gee" <[email protected]> wrote in message
> news:%[email protected]...
>> I suppose it would help if I included the macro!
>>
>> Range("A5:G5").Select
>> Selection.Insert Shift:=xlDown
>> Range("B3:F3").Select
>> Selection.Copy
>
> For efficiency, you should take out as much physical selection as
> poss. Forex, replace the last 2 lines with:
>
> Range("B3:F3").Copy
>
> Rgds,
> Andy

[Tushar Mehta]

It's easier than most people think. Here's a couple of pointers that I
use heavily.

For single statements just delete the Select and Selection. fragments
and merge the rest of the 2 statements. For multiple statements
enclose everything in a 'With' clause and delete all references to
'Selection'. [While they do work in 99.9+% of the cases, there are a
few instances where the mechanical application of the rules doesn't
work. And, it is also possible to better leverage the XL object model.
For some examples see 'Beyond the macro recorder' (http://www.tushar-
mehta.com/excel/vba/beyond_the_macro_recorder/index.htm)]

OK, so let's apply these tranformations to your code. The first three
actions are:

Range("A5:G5").Select
Selection.Insert Shift:=xlDown
Range("B3:F3").Select
Selection.Copy
Range("B5:F5").Select
Selection.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

These can be replaced by using the first rule. Delete the
'Select...Selection.' part to get:

Range("A5:G5").Insert Shift:=xlDown
Range("B3:F3").Copy
Range("B5:F5").PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Further down, the code does a bunch of things to cell B5:
Range("B5").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF(B5>B6,TRUE)"
Selection.FormatConditions(1).Font.ColorIndex = 5
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF(B5<B6,TRUE)"
Selection.FormatConditions(2).Font.ColorIndex = 3
Selection.Copy

The With clause rule applies here. The result is:
with Range("B5")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF(B5>B6,TRUE)"
.FormatConditions(1).Font.ColorIndex = 5
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF(B5<B6,TRUE)"
.FormatConditions(2).Font.ColorIndex = 3
.Copy
end with

And, that leaves one statement for you to transform:
Range("B5:F5").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:=
_
False, Transpose:=False

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
> Andy
>
> Thanks for that, have replaced as you have indicated, and it does run more
> smoothly!
>
> As I said, this macro was *recorded*, something even *I* can do!
> Editing is another matter!
>
> Thanks again.
>
> George Gee
>
>
> *Andy Brown* has posted this message:
>
> > "George Gee" <[email protected]> wrote in message
> > news:%[email protected]...
> >> I suppose it would help if I included the macro!
> >>
> >> Range("A5:G5").Select
> >> Selection.Insert Shift:=xlDown
> >> Range("B3:F3").Select
> >> Selection.Copy
> >
> > For efficiency, you should take out as much physical selection as
> > poss. Forex, replace the last 2 lines with:
> >
> > Range("B3:F3").Copy
> >
> > Rgds,
> > Andy
>
>
>
>

[George Gee]

Tushar

Many thanks for your time, I will have to study your changes
and see if I can understand them!

Your link seems to be broken!

George Gee

*Tushar Mehta* has posted this message:

> It's easier than most people think. Here's a couple of pointers that
> I use heavily.
>
> For single statements just delete the Select and Selection. fragments
> and merge the rest of the 2 statements. For multiple statements
> enclose everything in a 'With' clause and delete all references to
> 'Selection'. [While they do work in 99.9+% of the cases, there are a
> few instances where the mechanical application of the rules doesn't
> work. And, it is also possible to better leverage the XL object
> model. For some examples see 'Beyond the macro recorder'
> (http://www.tushar-
> mehta.com/excel/vba/beyond_the_macro_recorder/index.htm)]
>
> OK, so let's apply these tranformations to your code. The first three
> actions are:
>
> Range("A5:G5").Select
> Selection.Insert Shift:=xlDown
> Range("B3:F3").Select
> Selection.Copy
> Range("B5:F5").Select
> Selection.PasteSpecial Paste:=xlValues, _
> Operation:=xlNone, SkipBlanks:= _
> False, Transpose:=False
>
> These can be replaced by using the first rule. Delete the
> 'Select...Selection.' part to get:
>
> Range("A5:G5").Insert Shift:=xlDown
> Range("B3:F3").Copy
> Range("B5:F5").PasteSpecial Paste:=xlValues, _
> Operation:=xlNone, SkipBlanks:= _
> False, Transpose:=False
>
> Further down, the code does a bunch of things to cell B5:
> Range("B5").Select
> Selection.FormatConditions.Delete
> Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
> "=IF(B5>B6,TRUE)"
> Selection.FormatConditions(1).Font.ColorIndex = 5
> Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
> "=IF(B5<B6,TRUE)"
> Selection.FormatConditions(2).Font.ColorIndex = 3
> Selection.Copy
>
> The With clause rule applies here. The result is:
> with Range("B5")
> .FormatConditions.Delete
> .FormatConditions.Add Type:=xlExpression, Formula1:= _
> "=IF(B5>B6,TRUE)"
> .FormatConditions(1).Font.ColorIndex = 5
> .FormatConditions.Add Type:=xlExpression, Formula1:= _
> "=IF(B5<B6,TRUE)"
> .FormatConditions(2).Font.ColorIndex = 3
> .Copy
> end with
>
> And, that leaves one statement for you to transform:
> Range("B5:F5").Select
> Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
> SkipBlanks:=
> _
> False, Transpose:=False
>
> --
> Regards,
>
> Tushar Mehta
> www.tushar-mehta.com
> Excel, PowerPoint, and VBA add-ins, tutorials
> Custom MS Office productivity solutions
>
> In article <[email protected]>,
> [email protected] says...
>> Andy
>>
>> Thanks for that, have replaced as you have indicated, and it does
>> run more smoothly!
>>
>> As I said, this macro was *recorded*, something even *I* can do!
>> Editing is another matter!
>>
>> Thanks again.
>>
>> George Gee
>>
>>
>> *Andy Brown* has posted this message:
>>
>>> "George Gee" <[email protected]> wrote in message
>>> news:%[email protected]...
>>>> I suppose it would help if I included the macro!
>>>>
>>>> Range("A5:G5").Select
>>>> Selection.Insert Shift:=xlDown
>>>> Range("B3:F3").Select
>>>> Selection.Copy
>>>
>>> For efficiency, you should take out as much physical selection as
>>> poss. Forex, replace the last 2 lines with:
>>>
>>> Range("B3:F3").Copy
>>>
>>> Rgds,
>>> Andy

[Dave Peterson]

I think Tushar's post was hit by line wrap:

http://www.tushar-mehta.com/excel/vb...rder/index.htm

Worked ok.

George Gee wrote:
>
> Tushar
>
> Many thanks for your time, I will have to study your changes
> and see if I can understand them!
>
> Your link seems to be broken!
>
> George Gee
>
> *Tushar Mehta* has posted this message:
>
> > It's easier than most people think. Here's a couple of pointers that
> > I use heavily.
> >
> > For single statements just delete the Select and Selection. fragments
> > and merge the rest of the 2 statements. For multiple statements
> > enclose everything in a 'With' clause and delete all references to
> > 'Selection'. [While they do work in 99.9+% of the cases, there are a
> > few instances where the mechanical application of the rules doesn't
> > work. And, it is also possible to better leverage the XL object
> > model. For some examples see 'Beyond the macro recorder'
> > (http://www.tushar-
> > mehta.com/excel/vba/beyond_the_macro_recorder/index.htm)]
> >
> > OK, so let's apply these tranformations to your code. The first three
> > actions are:
> >
> > Range("A5:G5").Select
> > Selection.Insert Shift:=xlDown
> > Range("B3:F3").Select
> > Selection.Copy
> > Range("B5:F5").Select
> > Selection.PasteSpecial Paste:=xlValues, _
> > Operation:=xlNone, SkipBlanks:= _
> > False, Transpose:=False
> >
> > These can be replaced by using the first rule. Delete the
> > 'Select...Selection.' part to get:
> >
> > Range("A5:G5").Insert Shift:=xlDown
> > Range("B3:F3").Copy
> > Range("B5:F5").PasteSpecial Paste:=xlValues, _
> > Operation:=xlNone, SkipBlanks:= _
> > False, Transpose:=False
> >
> > Further down, the code does a bunch of things to cell B5:
> > Range("B5").Select
> > Selection.FormatConditions.Delete
> > Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
> > "=IF(B5>B6,TRUE)"
> > Selection.FormatConditions(1).Font.ColorIndex = 5
> > Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
> > "=IF(B5<B6,TRUE)"
> > Selection.FormatConditions(2).Font.ColorIndex = 3
> > Selection.Copy
> >
> > The With clause rule applies here. The result is:
> > with Range("B5")
> > .FormatConditions.Delete
> > .FormatConditions.Add Type:=xlExpression, Formula1:= _
> > "=IF(B5>B6,TRUE)"
> > .FormatConditions(1).Font.ColorIndex = 5
> > .FormatConditions.Add Type:=xlExpression, Formula1:= _
> > "=IF(B5<B6,TRUE)"
> > .FormatConditions(2).Font.ColorIndex = 3
> > .Copy
> > end with
> >
> > And, that leaves one statement for you to transform:
> > Range("B5:F5").Select
> > Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
> > SkipBlanks:=
> > _
> > False, Transpose:=False
> >
> > --
> > Regards,
> >
> > Tushar Mehta
> > www.tushar-mehta.com
> > Excel, PowerPoint, and VBA add-ins, tutorials
> > Custom MS Office productivity solutions
> >
> > In article <[email protected]>,
> > [email protected] says...
> >> Andy
> >>
> >> Thanks for that, have replaced as you have indicated, and it does
> >> run more smoothly!
> >>
> >> As I said, this macro was *recorded*, something even *I* can do!
> >> Editing is another matter!
> >>
> >> Thanks again.
> >>
> >> George Gee
> >>
> >>
> >> *Andy Brown* has posted this message:
> >>
> >>> "George Gee" <[email protected]> wrote in message
> >>> news:%[email protected]...
> >>>> I suppose it would help if I included the macro!
> >>>>
> >>>> Range("A5:G5").Select
> >>>> Selection.Insert Shift:=xlDown
> >>>> Range("B3:F3").Select
> >>>> Selection.Copy
> >>>
> >>> For efficiency, you should take out as much physical selection as
> >>> poss. Forex, replace the last 2 lines with:
> >>>
> >>> Range("B3:F3").Copy
> >>>
> >>> Rgds,
> >>> Andy

--

Dave Peterson

[Tushar Mehta]

Hi Dave,

Thanks for the fix.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>, ec35720
@netscapeXSPAM.com says...
> I think Tushar's post was hit by line wrap:
>
> http://www.tushar-mehta.com/excel/vb...rder/index.htm
>
> Worked ok.
>
{snip}

[michael.a7]

www.excelexchange.com

[David McRitchie]

Hi "michael a7", (not a real name)

Aside from the response having no apparent relationship to the question, ...

You are replying to a question that was asked at the beginning of last year, which
is a lot more than two weeks ago. This is the entire thread, and there were lots
of relevant answers. .
http://groups.google.com/groups?thre...TNGP09.phx.gbl
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"michael.a7" <[email protected]> wrote in message
news:[email protected]...
>
> www.excelexchange.com
>
>
> --
> michael.a7
> ------------------------------------------------------------------------
> michael.a7's Profile: http://www.excelforum.com/member.php...o&userid=33027
> View this thread: http://www.excelforum.com/showthread...hreadid=331600
>