Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Time stamp on multiple column

0

HI


I have a Macro script that when i have the input on column J, then both coolumn H & I will be updated automatically with the timestamp of when i input the column J. the macro worked perfectly.

Now i want to have similar Macro on column M ; when i input column M, both column K & L will be updated automatically with the timestamp.

Please help me to have 2 macros in the same workbook

I got the current macros from this youtube video : https://www.youtube.com/watch?v=gIPdx4-rqGg&t=663s&ab_channel=TeachExcel 

The macros script : 

Private Sub Worksheet_Change(ByVal Target As Range)

'  Timestamp Data

'   TeachExcel.com

Dim myTableRange As Range

Dim myDateTimeRange As Range

Dim myUpdatedRange As Range

'Your data table range

Set myTableRange = Range("J:J")

'Check if the changed cell is in the data tabe or not.

If Intersect(Target, myTableRange) Is Nothing Then Exit Sub

'Stop events from running

Application.EnableEvents = False

'Column for the date/time

Set myDateTimeRange = Range("H" & Target.Row)

'Column for last updated date/time

Set myUpdatedRange = Range("I" & Target.Row)

'Determine if the input date/time should change

If myDateTimeRange.Value = "" Then

    myDateTimeRange.Value = Now

End If

'Update the updated date/time value

myUpdatedRange.Value = Now

'Turn events back on

Application.EnableEvents = True

End Sub

thank you

Answer
Discuss

Discussion

Did my answer work for you?
John_Ru (rep: 6142) Oct 20, '22 at 12:50 pm
Did you try my Answer? If you can't ne bothered to respond, I suspect I just wasted my time trying to help you.
John_Ru (rep: 6142) Oct 25, '22 at 8:36 am
Add to Discussion

Answers

0

Hi Zakisarvi and welcome to the Forum

You can do that by:

  1. turning the first If statement  into a block If and negating the test i.e. run the code if a cell in column H was changed (also removing the "Exit Sub" ending)
  2. adding an Else clause which sets the range for second column (M:M) and applying the If ... Nothing...Exit Sub test, otherwise running repeated timestamp code aimed at columns K and L.

I've done that in the attached file and shown the chnages in bold below:

Private Sub Worksheet_Change(ByVal Target As Range)

'  Timestamp Data but with 2 columns

'   TeachExcel.com

Dim myTableRange As Range

Dim myDateTimeRange As Range

Dim myUpdatedRange As Range

'Your data table range

Set myTableRange = Range("J:J")

'Check if the changed cell is in the data table or not.

If Not Intersect(Target, myTableRange) Is Nothing Then ' Do NOT Exit Sub yet!

    'Stop events from running

    Application.EnableEvents = False

    'Column for the date/time

    Set myDateTimeRange = Range("H" & Target.Row)

    'Column for last updated date/time

    Set myUpdatedRange = Range("I" & Target.Row)

    'Determine if the input date/time should change

    If myDateTimeRange.Value = "" Then

        myDateTimeRange.Value = Now

    End If

    Else ' check second target range

    Set myTableRange = Range("M:M")

    If Intersect(Target, myTableRange) Is Nothing Then Exit Sub

    Application.EnableEvents = False

    Set myDateTimeRange = Range("K" & Target.Row)

    Set myUpdatedRange = Range("L" & Target.Row)

    If myDateTimeRange.Value = "" Then

        myDateTimeRange.Value = Now

    End If

End If

'Update the updated date/time value

myUpdatedRange.Value = Now


'Turn events back on

Application.EnableEvents = True

End Sub

There are slicker ways of doing it but this is the easiest to understand hopefully.  If this fixes your problem please don't forget to mark this Answer as selected.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login