Parsing Data Into Multiple Worksheets With Drop-Down Lists

     

         In this study, the row is copied to the sheet (e.g On_hire sheet) according to the value selected from the drop-down list in column H, and the row is deleted.

The following steps have been taken for data parsing into multiple sheets :
- "Options" name is defined for Range("J1:J3")

- Drop-down lists is created in Column H using Data Validation - List Method. The name we defined is entered to the "Source" section.

- The macro codes are added to Worksheet_Change Function in Vbe window :
"Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
If Target.Column = 8 Then
For i = 5 To Me.Cells(Rows.Count, 1).End(xlUp).Row
    If Cells(i, "H").Value = "On hire" Then
        Rows(i).Copy
        Sheets("On_hire").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Rows(i).Delete
        i = i - 1
    ElseIf Cells(i, "H").Value = "Off hire" Then
        Rows(i).Copy
        Sheets("Off_hire").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Rows(i).Delete
        i = i - 1
ElseIf Cells(i, "H").Value = "On sales" Then
        Rows(i).Copy
        Sheets("On_sales").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Rows(i).Delete
        i = i - 1
End If
Next i
End If
End Sub"