+ Reply to Thread
Results 1 to 5 of 5

Need multiple date stamps in ColA

Hybrid View

  1. #1
    Registered User
    Join Date
    07-14-2010
    Location
    Hot Springs, AR
    MS-Off Ver
    2010
    Posts
    38

    Need multiple date stamps in ColA

    I'm appending data to rows (ColB:ColL) in one w/s from another. Each time rows are appended I want a date stamp in ColA to be entered automatically. When multiple rows are appended the following code only puts the date in the first appended row.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 2 Or Target.Row = 1 Then Exit Sub
    Cells(Target.Row, 1) = Date
    End Sub
    Thanks,
    roothog

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Need multiple date stamps in ColA

    Maybe try something like this...

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Column <> 2 Or Target.Row = 1 Then Exit Sub
        Intersect(Range("A:A"), Target.EntireRow) = Date
    End Sub

  3. #3
    Registered User
    Join Date
    07-14-2010
    Location
    Hot Springs, AR
    MS-Off Ver
    2010
    Posts
    38

    Re: Need multiple date stamps in ColA

    That worked AlphaFrog, in putting the time stamp in each row copied. I didn't think it would make a difference and should've noted, but the data appended most often has blank rows at the bottom of the copy/pastespecial (19 rows copied each time filled top down). So, if the row is blank (always at the bottom) there needs to be no date stamp.

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Need multiple date stamps in ColA

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim cell As Range
        If Intersect(Range("B2:B" & Rows.Count), Target) Is Nothing Then Exit Sub
        On Error GoTo ReEnable
        Application.EnableEvents = False
        For Each cell In Intersect(Range("B2:B" & Rows.Count), Target)
            If cell.Value <> "" Then cell.Offset(, -1).Value = Date
        Next cell
    ReEnable:
        Application.EnableEvents = True
        If Err.Number <> 0 Then MsgBox Err.Number & " " & Err.Description, vbCritical, _
                                       "Error in Worksheet_Change Procedure"
    End Sub

  5. #5
    Registered User
    Join Date
    07-14-2010
    Location
    Hot Springs, AR
    MS-Off Ver
    2010
    Posts
    38

    Re: Need multiple date stamps in ColA

    a b s o l u t e l y perfect! thanks AlphaFrog for you time and knowledge for solving my problem!

    roothog

+ 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