Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Need Macro to insert current date & time into selected c

  1. #1
    Board Regular
    Join Date
    Mar 2007
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Need Macro to insert current date & time into selected c

    I use a excel file through the course of the day and need to insert the current date in one column and the current time in the next column. I want to be able to just highlight the selected range of cells I need to insert into and hit a macro button and have the date and time inserted into just the cells I have highlighted. I'm not sure how to make this work with just the cells I've highlighted. Any help any one can give me would be greatly appreciated. Thanks!
    Mike

  2. #2
    Board Regular
    Join Date
    Jul 2002
    Posts
    1,566
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Insert Date

    Hello,

    Welcome to the Board!

    Here is some code that should do it for you!


    Code:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean,  Cancel As Boolean)
    Range("A1") = Now        'Select any cell you want 
    End Sub
    HTH

    Again welcome to the board!

    Kurt

  3. #3
    Board Regular Datsmart's Avatar
    Join Date
    Jun 2003
    Location
    Olympia
    Posts
    7,985
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can also select the date cell and hit "Ctrl-;".
    Then select the time cell and hit "Ctrl-Shift-:".
    John

  4. #4
    Board Regular
    Join Date
    Mar 2007
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can also select the date cell and hit "Ctrl-;".
    Then select the time cell and hit "Ctrl-Shift-:".
    This is what I've been using but I would rather use a macro. I have a couple other steps I'm doing as well that I a;ready know how to do, but got stuck with at this point.
    Thanks

  5. #5
    Board Regular Datsmart's Avatar
    Join Date
    Jun 2003
    Location
    Olympia
    Posts
    7,985
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Code:
    Sub GetDateTime()
        ActiveCell.Value = Date
        ActiveCell.Offset(, 1).Value = Time
    End Sub
    John

  6. #6
    Board Regular
    Join Date
    Mar 2007
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Code:
    Sub GetDateTime()
    ActiveCell.Value = Date
    ActiveCell.Offset(, 1).Value = Time
    End Sub


    This is getting close to what I want, now if I select let's say range of a1 to b3, I would want it to post date and time 3 times. Is this possible?

  7. #7
    Board Regular Datsmart's Avatar
    Join Date
    Jun 2003
    Location
    Olympia
    Posts
    7,985
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Select A1:B1
    Run the macro code.
    Drag the fill handle in B1 down to B3 while holding Ctrl.
    (Holding Ctrl key stops number incrementing during the copy.)
    John

  8. #8
    Board Regular
    Join Date
    Mar 2007
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    But is it possible with a macro code?

  9. #9
    Board Regular Datsmart's Avatar
    Join Date
    Jun 2003
    Location
    Olympia
    Posts
    7,985
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What have you tried?

    This seems to work.
    Code:
    Sub GetDateTime()
        Set Rng = Selection
        For Each c In Rng
            If c.Column = ActiveCell.Column Then
                c.Value = Date
                c.Offset(, 1).Value = Time
            End If
        Next c
    End Sub
    John

  10. #10
    Board Regular
    Join Date
    Mar 2007
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Perfect! Thanks for all your help! MUCH appreciated!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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