Excel - How to insert rows when criteria is met?

April 2018

Issue
Solution
Note

Issue

I have two tables. If a field in table A is chosen, I want a macro which will insert rows of the data listed in table B into a new sheet.

For instance:
Column Table A Column Table B

Apple Mac 
Apple Green 
Apple Bartlett 

Pear 1 
Pear 2 
Pear 3

If I chose Apple, i want it in a new sheet :

Row 1 Mac 
Row 2 Green 
Row 3 Barlett

Thanks in advance for your help.

Solution

Introduce row1 and give column headings, suppose this is in sheet1

fruit type
Apple Mac
Apple Green
Apple Bartlett

Right click this sheets tab and click view code

in the resulting window copy this "event code".

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
Dim dest As Range, cfind As Range, x As String 
Dim j As Integer, y As String, add As String 
If Target.Column <> 1 Then Exit Sub 
x = Target.Value 
Set cfind = Cells.Find(what:=x) 
j = cfind.Row 
x = cfind.Offset(0, 1).Value 
add = cfind.Address 
With Worksheets("sheet2") 
Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) 
dest = "row" & " " & j 
dest.Offset(0, 1) = x 
End With 
Do 
Set cfind = Cells.FindNext(cfind) 
If cfind Is Nothing Then Exit Do 
If cfind.Address = add Then Exit Do 
j = cfind.Row 
x = cfind.Offset(0, 1).Value 
With Worksheets("sheet2") 
Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) 
dest = "row" & " " & j 
dest.Offset(0, 1) = x 
End With 
Loop 
End Sub

Now just click apple in sheet 1 and see what you get in sheet 2

The macro undo clears sheet2 so that you can again choose "apple" and
recheck .

Sub undo() 
Worksheets("sheet2").Cells.Clear 
End Sub

Note

Thanks to venkat1926 for this tip on the forum.

Published by aakai1056. Latest update on May 10, 2010 at 07:42 AM by aakai1056.

Excel - Comparing 2 columns and removing duplicates

Excel - Match a string to a column & increment by 1

Excel - How to insert rows when criteria is met?

Issue

Solution

Note

Related