I was using VBA code for Timestamp And move row to another worksheet. I was using Below codes successfully working individually, but two codes entered in worksheet module not working.
I hope you help me.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
If Target.value = "" Then
Range("B1").value = ""
Else
Range("B1").value = Format(Now, "dd/mm/yyyy HH:mm:ss AM/PM")
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("g:g")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target = "yes" Then
Target.EntireRow.Copy Sheets("s2").Cells(Sheets("s2").Rows.Count, "A").End(xlUp).Offset(0)
End If
Application.EnableEvents = True
End Sub
Each sheet can have only one Worksheet_Change
event sub, which is why you're seeing a compiler error. However, you can merge your two scripts into a single Worksheet_Change event.
Note: Offset(0)
means no offset, so the code below overwrites the last data row on sheet s2, which is likely not the intended outcome. Changing it to Offset(1) will copy the data to the first blank row.
Target.EntireRow.Copy Sheets("s2").Cells(Sheets("s2").Rows.Count, "A").End(xlUp).Offset(0)
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
If Target.Value = "" Then
Me.Range("B1").Value = ""
Else
Me.Range("B1").Value = Format(Now, "dd/mm/yyyy HH:mm:ss AM/PM")
End If
End If
If Not Intersect(Target, Me.Range("g:g")) Is Nothing Then
If Target.Value = "yes" Then
Target.EntireRow.Copy Sheets("s2").Cells(Sheets("s2").Rows.Count, "A").End(xlUp).Offset(1)
End If
End If
Application.EnableEvents = True
End Sub
Microsoft documentation: