+ Reply to Thread
Results 1 to 13 of 13

Insert date in macro

  1. #1
    George Gee
    Guest

    Insert date in macro

    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
    --



  2. #2
    Andy Brown
    Guest

    Re: Insert date in macro

    > 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



  3. #3
    Harald Staff
    Guest

    Re: Insert date in macro

    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" <george.nomaps@ntlworld.xom> skrev i melding
    news:u7b0Hp$7EHA.1524@TK2MSFTNGP09.phx.gbl...
    > 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
    > --
    >
    >




  4. #4
    George Gee
    Guest

    Re: Insert date in macro

    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
    > --




  5. #5
    George Gee
    Guest

    Re: Insert date in macro

    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" <george.nomaps@ntlworld.xom> skrev i melding
    > news:u7b0Hp$7EHA.1524@TK2MSFTNGP09.phx.gbl...
    >> 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
    >> --




  6. #6
    Andy Brown
    Guest

    Re: Insert date in macro

    "George Gee" <george.nomaps@ntlworld.xom> wrote in message
    news:%23ncJTw$7EHA.3820@TK2MSFTNGP11.phx.gbl...
    > 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



  7. #7
    George Gee
    Guest

    Re: Insert date in macro

    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" <george.nomaps@ntlworld.xom> wrote in message
    > news:%23ncJTw$7EHA.3820@TK2MSFTNGP11.phx.gbl...
    >> 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





  8. #8
    Tushar Mehta
    Guest

    Re: Insert date in macro

    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 <uQdbbMA8EHA.3336@TK2MSFTNGP11.phx.gbl>,
    george.nomaps@ntlworld.xom 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" <george.nomaps@ntlworld.xom> wrote in message
    > > news:%23ncJTw$7EHA.3820@TK2MSFTNGP11.phx.gbl...
    > >> 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

    >
    >
    >
    >


  9. #9
    George Gee
    Guest

    Re: Insert date in macro

    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 <uQdbbMA8EHA.3336@TK2MSFTNGP11.phx.gbl>,
    > george.nomaps@ntlworld.xom 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" <george.nomaps@ntlworld.xom> wrote in message
    >>> news:%23ncJTw$7EHA.3820@TK2MSFTNGP11.phx.gbl...
    >>>> 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





  10. #10
    Dave Peterson
    Guest

    Re: Insert date in macro

    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 <uQdbbMA8EHA.3336@TK2MSFTNGP11.phx.gbl>,
    > > george.nomaps@ntlworld.xom 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" <george.nomaps@ntlworld.xom> wrote in message
    > >>> news:%23ncJTw$7EHA.3820@TK2MSFTNGP11.phx.gbl...
    > >>>> 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

  11. #11
    Tushar Mehta
    Guest

    Re: Insert date in macro

    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 <41D743EB.C77746A5@netscapeXSPAM.com>, 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}

  12. #12
    Registered User
    Join Date
    03-31-2006
    Posts
    27

  13. #13
    David McRitchie
    Guest

    Re: Insert date in macro

    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" <michael.a7.26cv0y_1145187901.9197@excelforum-nospam.com> wrote in message
    news:michael.a7.26cv0y_1145187901.9197@excelforum-nospam.com...
    >
    > 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
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1