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

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

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

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: