Hi
I have the code attached, its an auto sheet event script
How do I get the vba code to read the value and not the formula? I need it to move the rows depending on the status in column A and that changes based on the values in other columns.
I’ve put the code I’m using below. It works perfectly if I actually type in the phrase I’m using.
Thanks for any help ?
Claire
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Application.ScreenUpdating = False
Dim Lastrow As Long
Dim Lastrowa As Long
Lastrow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
Lastrowa = Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row + 1
If Target.Value = "To be Booked" Then
Rows(Target.Row).Copy Destination:=Sheets("Sheet2").Rows(Lastrow)
Application.EnableEvents = False
Rows(Target.Row).Delete
Application.EnableEvents = True
Exit Sub
End If
Application.EnableEvents = True
If Target.Value = "Booked" Then
Rows(Target.Row).Copy Destination:=Sheets("Sheet3").Rows(Lastrowa)
Application.EnableEvents = False
Rows(Target.Row).Delete
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
I have the code attached, its an auto sheet event script
How do I get the vba code to read the value and not the formula? I need it to move the rows depending on the status in column A and that changes based on the values in other columns.
I’ve put the code I’m using below. It works perfectly if I actually type in the phrase I’m using.
Thanks for any help ?
Claire
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Application.ScreenUpdating = False
Dim Lastrow As Long
Dim Lastrowa As Long
Lastrow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
Lastrowa = Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row + 1
If Target.Value = "To be Booked" Then
Rows(Target.Row).Copy Destination:=Sheets("Sheet2").Rows(Lastrow)
Application.EnableEvents = False
Rows(Target.Row).Delete
Application.EnableEvents = True
Exit Sub
End If
Application.EnableEvents = True
If Target.Value = "Booked" Then
Rows(Target.Row).Copy Destination:=Sheets("Sheet3").Rows(Lastrowa)
Application.EnableEvents = False
Rows(Target.Row).Delete
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub