Parsing Data Into Multiple Worksheets With Drop-Down Lists

Excel VBA: Split Data Based On Cell Value


          We created the data validation lists for each row in column H of Sheet1 worksheet. There are 3 values in this drop-down list: On hire, Off hire, On sales.
We have created 3 worksheets with these names. The user selects a value from the drop-down list in column H, that row is cut and pasted to the page with the same name as the drop-down list.
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


excel parsing data

No comments:

Post a Comment