I enter data on sheet1 in various rows and columns. I am trying to get a date to display in sheet2 each time i enter a value in sheet1.
This is what i have in Sheet1:
`Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("I7:NF1000")) Is Nothing Then Sheet2.Range("E7:E") = Now End If End Sub`
The code works but the issue is that whenever a cell in Sheet1 range is updated it enters the date in all the cells in column E in Sheet2. How do i get it to only display date in the corresponding cell on sheet2. Eg. if i enter a value in I8 on Sheet1, i want the date to come across in E8 on sheet2. I can do it with each individual row but that would mean i would need to copy the If Not function a 1000 times to cover all the rows.. see below:
`If Not Intersect(Target, Range("I8:NF8")) Is Nothing Then Sheet2.Range("E8") = Now End If If Not Intersect(Target, Range("I9:NF9")) Is Nothing Then Sheet2.Range("E9") = Now End If`
and so on.... is there a way to avoid this and have a simpler code that will do this without me having 1000 lines on code..
CodePudding user response:
A Worksheet Change: Time Stamp in Another Worksheet
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim srg As Range: Set srg = Range("I7:NF1000")
Dim irg As Range: Set irg = Intersect(srg, Target)
If irg Is Nothing Then Exit Sub
Set irg = Intersect(irg.EntireRow, Columns("E"))
Dim TimeStamp As Date: TimeStamp = Now
Dim arg As Range
For Each arg In irg.Areas
Sheet2.Range(arg.Address).Value = TimeStamp
Next arg
End Sub