VBA move to different worksheet depending on the cell value

[marujamarujjjj]

Can somebody please help me. I am just starting to learn VBA. Is it possible to use two codes in one sheet? Cause I can't quite figure it out and I don't know how to combine the commands.
So, I've got the Sheet1 with drop-downlist on Column L.
I want to transfer to another worksheet if the cell says "quoted" and to another cell if it its "deferred".
These are the codes I used for "Quoted" worksheet, but I don't know what to do to transfer data to another worksheet - "Deferred". Please help.

under worksheet:
Private Sub Worksheet_Change(ByVal Target As Range)

'Subscribe to youtube.com/excel10tutorial

Dim Z As Long

Dim xVal As String

On Error Resume Next

If Intersect(Target, Range("L:L")) Is Nothing Then Exit Sub

Application.EnableEvents = False

For Z = 1 To Target.Count

If Target(Z).Value > 0 Then

Call MoveBasedOnValue

End If

Next

Application.EnableEvents = True

End Sub



Under Module:
Sub MoveBasedOnValue()

Dim xRg As Range

Dim xCell As Range

Dim A As Long

Dim B As Long

Dim C As Long

A = Worksheets("BCI Projects").UsedRange.Rows.Count

B = Worksheets("Quoted").UsedRange.Rows.Count

If B = 1 Then

If Application.WorksheetFunction.CountA(Worksheets("Quoted").UsedRange) = 0 Then B = 0

End If

Set xRg = Worksheets("BCI Projects").Range("L1:L" & A)

On Error Resume Next

Application.ScreenUpdating = False

For C = 1 To xRg.Count

If CStr(xRg(C).Value) = "quoted" Then

xRg(C).EntireRow.Copy Destination:=Worksheets("Quoted").Range("A" & B + 1)

xRg(C).EntireRow.Delete

If CStr(xRg(C).Value) = "quoted" Then

C = C - 1

End If

B = B + 1

End If

Next

Application.ScreenUpdating = True

End Sub

 

[jolivanes]

Try so (in sheet module)

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("L1")
Select Case Target.Value
Case Is = "quoted"

'<---- Do you stuff here for when it is "quoted".

Case Is = "deferred"

'<---- Do you stuff here for when it is "deferred".

Case Else
End Select
End Sub

BTW, your code will be readable if you use code tags.

Your "prefer not to say" for office version is a hindrance as quite often code is tailored to the version people use.

 

[jolivanes]

The "Please help." in your post ranks about the same as "Thank you for giving your time to help me."

 

[jolivanes]

Re: "I am just starting to learn VBA"
Included in learning is thanking people for their help.