Macro to add a date everytime a table is updated

[tonywatsonhelp]

Hi everyone,
I'd like a macro that can tell me when i make changes to any data in a table

The table is on sheet "marketing" and is just called Table4
the range is A10:I and last row if thats needed

so this table has several columns that i update from time to time, and what I'd like is if i change a cell it adds todays date into column i (Called "Last Updated" in the table) of the row i updated.
please can you help,
Thanks
Tony

 

[Candyman8019]

Give this a try:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lRow As Long

If Target.Column < 9 And Target.Row <= 100 Then
lRow = Cells(Rows.Count, 9).End(xlUp).Row + 1


Sheets("Sheet4").Cells(lRow, 9).Value = Date
End If

End Sub

If any cell from A1:H100 is updated then the date will be added to the list of dates in column I.

 

[mumps]

Which columns do you update from time to time?

 

[Candyman8019]

Oh, I just reread your initial request and you wanted to add a date next to the changed row...give this a try:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column < 9 And Target.Row <= 100 Then

Sheets("Sheet4").Cells(Target.Row, 9).Value = Date

End If
End Sub


Change "sheet4" to whatever your sheet is called. You can adjust the max number of rows it will look at as well by changing 100 to 1000 for example.

 

[Misca]

Try something like:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A10").CurrentRegion) Is Nothing Then

    Application.EnableEvents = False
    Range("I" & Target.Row).Value = Date
    Application.EnableEvents = True
End If

End Sub

Place the code on the sheet module of your "Marketing" sheet and adjust it to your liking.

 

[tonywatsonhelp]

Brilliant,
thank you to everyone for your help
this is great
Tony

 

[JoeMo]

Tony, here's another change event macro you can try. This one is able to handle a change in multiple table cells/rows (e.g. by pasting to multiple cells) where Target is more than one cell, and is immune to relocating the table in a way that the update column is no longer in column I.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LO As ListObject, c As Range
Set LO = Me.ListObjects("Table4")
If Not Intersect(Target, LO.DataBodyRange.Resize(, LO.ListColumns.Count - 1)) Is Nothing Then
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    For Each c In Intersect(Target, LO.DataBodyRange.Resize(, LO.ListColumns.Count - 1))
        Cells(c.Row, LO.DataBodyRange.Columns(LO.DataBodyRange.Columns.Count).Column).Value = Date
    Next c
    With Application
        .ScreenUpdating = False
        .EnableEvents = True
    End With
End If
LO.DataBodyRange.Columns(LO.DataBodyRange.Columns.Count).EntireColumn.AutoFit
End Sub