Excel Parsing Data Into Multiple Worksheets With Drop-Down Lists In Column

January 19, 2017 Leave a comment

excel parsing data

In this study, the row is copied to the 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”

Download Example Workbook

Search Within The Entire Workbook Matches To Cell Value

January 15, 2017 Leave a comment

When the cell that to be searched is double-clicked,userform opens and  the matching cells  can be seen as page name and cell address on the userform.

excel matching data

Example workbook can be downloaded here