How to add a second macro for changes in a different table range to existing macro

0

This is the existing macro I copied previously but I want to add a date and time stamp for modified only in a different table range as this is for new and modified only? I have tried to change this but to no avail. Please help!

Private Sub Worksheet_Change(ByVal Target As Range)

'Timestamp Data

Dim myTableRange As Range

Dim myDateTimeRange As Range

Dim myUpdatedRange As Range

'Your data table range

Set myTableRange = Range("E3013:BB100000")

'Check if the changed cell is in the data table 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("B" & Target.Row)

'Column for last updated date/time

Set myUpdatedRange = Range("C" & 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

Answer
Discuss

Discussion

Please put CODE tags around your code. To do that, edit your post, select your code and click the CODE button.
don (rep: 1969) Feb 15, '21 at 12:51 pm
Add to Discussion

Answers

0
Selected Answer

Hi Cozza and welcome to the Forum

Suggest you replace your code with the code below.

I've highlighted bits in bold to show changes (and added comments). Essentially I've added two variables for your second "check" (myDataRange2 and myUpdatedRange2). Then, where the original macro exits (if Target isn't in the first range), I've added NOT in the If statement (and removed Exit sub) then done your code. 

The Else to that is a second If....Intersect statement (without the Not) which exits (if the target wasn't in either of your ranges). The Else has code for the updated time/date (but removes the IF for the initial timestamp).

You need to change the ranges in bold so that they match what you need.

Private Sub Worksheet_Change(ByVal Target As Range)

'Timestamp Data

Dim myTableRange As Range, myTableRange2 As Range ' add second range

Dim myDateTimeRange As Range

Dim myUpdatedRange As Range, myUpdatedRange2 As Range 'add second update range

'Your data table range

Set myTableRange = Range("E3013:BB100000")
Set myTableRange2 = Range("BC3013:BE100000")

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

If Not Intersect(Target, myTableRange) Is Nothing Then 'update date and/or updated if in FIRST range

    'Stop events from running

    Application.EnableEvents = False

    'Column for the date/time

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

    'Column for last updated date/time

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

    'Determine if the input date/time should change

    If myDateTimeRange.Value = "" Then

        myDateTimeRange.Value = Now
        'Update the updated date/time value

    End If

        myUpdatedRange.Value = Now

        'Turn events back on

        Application.EnableEvents = True



    Else:

    If Intersect(Target, myTableRange2) Is Nothing Then Exit Sub 'quit if not in SECOND range either

        'Stop events from running

        Application.EnableEvents = False

        'Column for last updated date/time

        Set myUpdatedRange2 = Range("C" & Target.Row)

        'Update the updated date/time value

        myUpdatedRange2.Value = Now

        'Turn events back on

        Application.EnableEvents = True

End If


End Sub
Hope this solves your problem.

(Revision 1: just corrected typos). 

Discuss

Discussion

Many thanks John_Ru it works perfectly.
Cozza (rep: 2) Feb 15, '21 at 1:12 pm
Cozza

That's good. 
John_Ru (rep: 1002) Feb 15, '21 at 1:49 pm
Add to Discussion


Answer the Question

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