Excel - How to insert rows when criteria is met?

June 2017




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.

Related


Published by aakai1056. Latest update on May 10, 2010 at 07:42 AM by aakai1056.
This document, titled "Excel - How to insert rows when criteria is met?," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).