VBA reading cell value not formula

[ClaireLou]

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

 

[Fluff]

Change events do not get triggered by a formula.
Does the formula in col A look only at that sheet, or does it depend on values on another sheet?

 

[ClaireLou]

Change events do not get triggered by a formula.
Does the formula in col A look only at that sheet, or does it depend on values on another sheet?

Hi
It only looks at the one sheet
Thanks

 

[Fluff]

Ok, how about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim KyCell As Range
   
   If Target.CountLarge > 1 Then Exit Sub
   Set KyCell = Range("A:A")
   On Error Resume Next
   Set KyCell = Union(KyCell, KyCell.Precedents)
   On Error GoTo 0
   If Not Intersect(Target, KyCell) Is Nothing Then
      Select Case Range("A" & Target.Row).Value
         Case "To be Booked"
            With Target.EntireRow
               .Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
               .Delete
            End With
         Case "Booked"
            With Target.EntireRow
               .Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1)
               .Delete
            End With
      End Select
   End If
End Sub

 

[ClaireLou]

Ok, how about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim KyCell As Range
  
   If Target.CountLarge > 1 Then Exit Sub
   Set KyCell = Range("A:A")
   On Error Resume Next
   Set KyCell = Union(KyCell, KyCell.Precedents)
   On Error GoTo 0
   If Not Intersect(Target, KyCell) Is Nothing Then
      Select Case Range("A" & Target.Row).Value
         Case "To be Booked"
            With Target.EntireRow
               .Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
               .Delete
            End With
         Case "Booked"
            With Target.EntireRow
               .Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1)
               .Delete
            End With
      End Select
   End If
End Sub

It’s not doing anything. Now when I change the target cell manually, it’s not changing either

 

[Dossfm0q]

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" And Target.HasFormula = False Then

Rows(Target.Row).Copy Destination:=Sheets("Sheet2").Rows(Lastrow)
MsgBox Rows(Target.Row).Address
Exit Sub

Application.EnableEvents = False
Rows(Target.Row).Delete
Application.EnableEvents = True
End If
Application.EnableEvents = True
If Target.Value = "Booked" And Target.HasFormula = False 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

 

[Fluff]

It’s not doing anything

As VBA is case sensitive are the col A values exactly "Booked" and "To be Booked"?

@Dossfm0q
The OP has stated that col A has a formula, therefore that code will not work.

 

[ClaireLou]

I think it might be the entry of multiple cells at once. Do you guys know a better code, even one that I have to run manually, that will move the row based on the output in column A? Booked & To be booked still need to go to separate sheets and blank to stay on the original. Thanks

 

[Fluff]

What is the name of the sheet that has the data?

 

[ClaireLou]

What is the name of the sheet that has the data?

Sheet1

Thanks so much for your help