+ Reply to Thread
Results 1 to 4 of 4

Date - Automatic

Hybrid View

  1. #1
    Registered User
    Join Date
    04-05-2012
    Location
    Canada
    MS-Off Ver
    Excel 2003/2007
    Posts
    45

    Date - Automatic

    Hi. I have a scenario that I need help. Here it is:

    I have sheet1 and sheet2

    On A1 (Sheet1), I have a macro that when I hit ENTER key after inputting info it will automatically copy it to last empty row on Sheet2 (starting from column A):

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range, MyArr As Variant, NR As Long
    
    For Each cell In Target
        If cell.Address = "$A$1" And Len(cell) > 0 Then
            MyArr = Split(cell, "-")
            With Sheets("Sheet2")
                NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1
                .Range("A" & NR).Value = MyArr(1)
                .Range("B" & NR).Value = MyArr(0)
                .Range("C" & NR).Value = MyArr(2)
            End With
            cell.Value = ""
            cell.Select
        End If
    Next cell
    
    End Sub
    Now, what I would like to add here is after it got copied to Sheet2, I would like to add the date it is entered (which is current date) automatically on column E(Sheet2). Please help. Thank you again.

    Note: The code above is provided to me by one best Excel pro here in this forum. I thank him for that.

  2. #2
    Valued Forum Contributor
    Join Date
    05-07-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    354

    Re: Date - Automatic

    .Range("E" & NR).Value = now()
    Regards,
    Vandan

  3. #3
    Registered User
    Join Date
    04-05-2012
    Location
    Canada
    MS-Off Ver
    Excel 2003/2007
    Posts
    45

    Re: Date - Automatic

    thank you very much. It worked.

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Date - Automatic

    Just
    .Range("E" & NR).Value = Format(Date,"short date")
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

+ 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