How to move a row to the next sheet when I input a certain value in a cell within that row?

[jdlerry]

NEED HELP PLEASE. Thank you! I actually asked the same question before which is VBA codes. If someone can help me with the VBA code please.

 

[Zot]

Be more precise:
Where do you want to input value?
By move, you mean delete the entire row (including your input data) to the next sheet on same row or start moving from row 1 and so on?

 

[jdlerry]

Be more precise:
Where do you want to input value?
By move, you mean delete the entire row (including your input data) to the next sheet on same row or start moving from row 1 and so on?

Thank you for your reply. I really appreciate it. If you can take a look at the file that I saved here on Dropbox. Basically I will put my data in Masterlist tab and under ACTION TAKEN/ TO DO, whenever I select an action, it should move to another tab. You may see the INSTRUCTION tab for reference https://www.dropbox.com/scl/fi/o1l7v9vxhgvw2871it1jl/eMedical-Tracker-new.xlsx?dl=0&rlkey=rkmrvmi86eswnf1iegsyxp2cf

 

[jdlerry]

Be more precise:
Where do you want to input value?
By move, you mean delete the entire row (including your input data) to the next sheet on same row or start moving from row 1 and so on?

The entire row should move to the next tab when I select a data from ACTION TAKEN/ TO DO. For example in this screenshot, the entire row 3 (DATE until SERVICE TICKET #) should also move when I change the ACTION TAKEN/TO DO. Hope I did not confuse you. Thank you

 

[jdlerry]

Be more precise:
Where do you want to input value?
By move, you mean delete the entire row (including your input data) to the next sheet on same row or start moving from row 1 and so on?

and yes, delete the entire row and move to another tab

 

[jdlerry]

The entire row should move to the next tab when I select a data from ACTION TAKEN/ TO DO. For example in this screenshot, the entire row 3 (DATE until SERVICE TICKET #) should also move when I change the ACTION TAKEN/TO DO. Hope I did not confuse you. Thank you

It should be row 4 rather. Not row 3 that will move. Thank you

 

[Zot]

Put this code under Masterlist module to auto trigger when selection is made. I'm just guessing the destination. You can modify to your need. Easy to understand

Private Sub Worksheet_Change(ByVal Target As Range)

Dim strAction As String
Dim rngData As Range
Dim wsDest As Worksheet

Application.EnableEvents = False
On Error Resume Next

If Not Intersect(Range("N4"), Target) Is Nothing Then
    Set rngData = ActiveSheet.Range("A4", "V4")
    Select Case Target
        Case "FOR CASERVICEDESK"
            
        Case "TRIGGERED IN EDP"
        
        Case "REFER TO CON OFF"
            Set wsDest = Sheets("CON OFF")
            rngData.Copy
            wsDest.Range("A" & rowNext(wsDest)).PasteSpecial (xlPasteValues)
            rngData.ClearContents
        Case "TERM 1"
            Set wsDest = Sheets("TERM 1")
            rngData.Copy
            wsDest.Range("A" & rowNext(wsDest)).PasteSpecial (xlPasteValues)
            rngData.ClearContents
        Case "EMAIL SENT TO CST"
            Set wsDest = Sheets("EMAIL SENT TO CST")
            rngData.Copy
            wsDest.Range("A" & rowNext(wsDest)).PasteSpecial (xlPasteValues)
            rngData.ClearContents
        Case "FF-UP EMAIL SENT (CST)"
            Set wsDest = Sheets("FF-UP EMAIL SENT (CST)")
            rngData.Copy
            wsDest.Range("A" & rowNext(wsDest)).PasteSpecial (xlPasteValues)
            rngData.ClearContents
        Case "VERIFY WITH SLEC"
            Set wsDest = Sheets("VERIFY WITH SLEC")
            rngData.Copy
            wsDest.Range("A" & rowNext(wsDest)).PasteSpecial (xlPasteValues)
            rngData.ClearContents
        Case "RESOLVED"
            Set wsDest = Sheets("RESOLVED")
            rngData.Copy
            wsDest.Range("A" & rowNext(wsDest)).PasteSpecial (xlPasteValues)
            rngData.ClearContents
        Case "OTHERS, SEE REMARKS"
            Set wsDest = Sheets("OTHERS")
            rngData.Copy
            wsDest.Range("A" & rowNext(wsDest)).PasteSpecial (xlPasteValues)
            rngData.ClearContents
    End Select
End If

On Error GoTo 0
Application.EnableEvents = True

End Sub

Function rowNext(ws As Worksheet) As Long
rowNext = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
End Function

 

[jdlerry]

Hi, Thank you for this. But it's not doing anything.

 

[jdlerry]

Put this code under Masterlist module to auto trigger when selection is made. I'm just guessing the destination. You can modify to your need. Easy to understand

Private Sub Worksheet_Change(ByVal Target As Range)

Dim strAction As String
Dim rngData As Range
Dim wsDest As Worksheet

Application.EnableEvents = False
On Error Resume Next

If Not Intersect(Range("N4"), Target) Is Nothing Then
    Set rngData = ActiveSheet.Range("A4", "V4")
    Select Case Target
        Case "FOR CASERVICEDESK"
           
        Case "TRIGGERED IN EDP"
       
        Case "REFER TO CON OFF"
            Set wsDest = Sheets("CON OFF")
            rngData.Copy
            wsDest.Range("A" & rowNext(wsDest)).PasteSpecial (xlPasteValues)
            rngData.ClearContents
        Case "TERM 1"
            Set wsDest = Sheets("TERM 1")
            rngData.Copy
            wsDest.Range("A" & rowNext(wsDest)).PasteSpecial (xlPasteValues)
            rngData.ClearContents
        Case "EMAIL SENT TO CST"
            Set wsDest = Sheets("EMAIL SENT TO CST")
            rngData.Copy
            wsDest.Range("A" & rowNext(wsDest)).PasteSpecial (xlPasteValues)
            rngData.ClearContents
        Case "FF-UP EMAIL SENT (CST)"
            Set wsDest = Sheets("FF-UP EMAIL SENT (CST)")
            rngData.Copy
            wsDest.Range("A" & rowNext(wsDest)).PasteSpecial (xlPasteValues)
            rngData.ClearContents
        Case "VERIFY WITH SLEC"
            Set wsDest = Sheets("VERIFY WITH SLEC")
            rngData.Copy
            wsDest.Range("A" & rowNext(wsDest)).PasteSpecial (xlPasteValues)
            rngData.ClearContents
        Case "RESOLVED"
            Set wsDest = Sheets("RESOLVED")
            rngData.Copy
            wsDest.Range("A" & rowNext(wsDest)).PasteSpecial (xlPasteValues)
            rngData.ClearContents
        Case "OTHERS, SEE REMARKS"
            Set wsDest = Sheets("OTHERS")
            rngData.Copy
            wsDest.Range("A" & rowNext(wsDest)).PasteSpecial (xlPasteValues)
            rngData.ClearContents
    End Select
End If

On Error GoTo 0
Application.EnableEvents = True

End Sub

Function rowNext(ws As Worksheet) As Long
rowNext = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
End Function

Not working

 

[Zot]

It is an event trigger code. See image below. You need to double-click the Masterlist worksheet and paste the code there. No need to Insert Module or anything