Kapat

Excel Macro To Parse Data Into Sheets

excel vba parsing data Excel VBA Parsing Rows
Into Sheets

1️⃣ Firstly, we created the data validation lists for cells in column H of worksheets. There are 3 values in this drop-down lists: On hire, Off hire, On sales.

We followed these steps to create data validation lists :
excel define name “Options” name is defined for Range(“J1:J3”).excel define name

excel data validation list Drop-down lists is created in Column H using Data Validation – List menu. “Option” name that we defined it is entered to the “Source” section.
excel create data validation list on column

2️⃣ Later ,we created worksheets with the same name as the values in the drop-down list: excel drop down list item On_hire, excel combobox item Off_hire, On_sales sheets.
When the user selected a value from the drop-down list in column H, that row is copied and pasted to the sheet with the same name as the drop-down list value. The copied row is removed from the active sheet.

We added the following VBA codes that we will use for row copying and pasting to the Sheet1 Worksheet_Change procedure : excel vba copying pasting

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 Me.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

📥 Download sample Excel workbook