Solved Macro t copy multiple rows (same column value) to new sheet

November 3, 2015 at 11:38:22
Specs: Windows 7
New to Excel Macros, tried this from a thread from 2010, worked very well. Only thing is, in my worksheets, I have multiple rows with the same column value. I need to copy and paste them all into sheet 2, and then move on to the next gene, and so on.

Sub GeneFinder()
Dim srchLen, gName, nxtRw As Integer
Dim g As Range
'Clear Sheet 2 and Copy Column Headings
Sheets(1).Rows(1).Copy Destination:=Sheets(2).Rows(1)
'Determine length of Search Column from Sheet3
srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
'Loop through list in Sheet3, Column A. As each value is
'found in Sheet1, Column I, copy it to the next row in Sheet2
With Sheets(1).Columns("I")
For gName = 2 To srchLen
Set g = .Find(Sheets(3).Range("A" & gName), lookat:=xlWhole)
If Not g Is Nothing Then
nxtRw = Sheets(2).Range("D" & Rows.Count).End(xlUp).Row + 1
g.EntireRow.Copy Destination:=Sheets(2).Range("A" & nxtRw)
End If
End With
End Sub

Anyone out there that can help? Thank you.

See More: Macro t copy multiple rows (same column value) to new sheet

Report •

November 3, 2015 at 12:33:56
✔ Best Answer
First, may I request that you click on the blue line at the bottom of this post and read the instructions related to posting VBA code in this forum. Please keep those instructions in mind the next time you post VBA code. Thanks!

As for your question, this thread contains the solution.

In addition, this How To should help you write and debug VBA code. If you are going to be writing/editing VBA code, these techniques can be extremely helpful.

The infamous blue line:

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

November 10, 2015 at 02:23:29
Thank you DerbyDad03,

I think it has worked, with a little tweaking. I've learned a lot! You have saved me lots of time.

Thanks again,

Report •

Related Solutions

Ask Question