VBA Code to look for a Value in a Column and Copy entire row to another sheet.

[zerlotus]

Hello everyone,

I'm looking for a simple code that:

Based on a cell value in Sheet 1 (A21), it looks for it in Sheet 2 column A. Then it copies the entire row and paste in A2 Sheet 3.

I tried several ways, look for guidance in the forum/google, but could not find a thing that i could use. They all were to complex as to me being able to simplify it.

Could someone help me?

Thank you.

 

[alansidman]

Here is an untested (you did not provide sample data to test) VBA module.

Option Explicit

Sub Zerlotus()
    Dim s1 As Worksheet, s2 As Worksheet, s3 As Worksheet
    Dim crit, lr2 As Long, lr3 As Long
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Set s3 = Sheets("Sheet3")
    crit = s1.Range("A21")
    lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
    Dim i As Long
    For i = 2 To lr2    'assumes row 1 has a header. Start search in row 2
        If s2.Range("A" & i) = crit Then
            s2.Range("A" & i).EntireRow.Copy
            s3.Range("A2").PasteSpecial xlPasteValues
        End If
    Next i
    Application.CutCopyMode = False
    MsgBox "Found"
End Sub

 

[DanteAmor]

Assuming your data like this:

Dante Amor
	A	B
1
21	some
22
Sheet1

Dante Amor
	A	B	C	D
1	a	b	c	d
2	some	b2	c2	d2
3	a3	b3	c3	d3
4	a4	b4	c4	d4
5	some	b5	c5	d5
6	a6	b6	c6	d6
7	some	b7	c7	d7
8	a8	b8	c8	d8
9	some	b9	c9	d9
10	a10	b10	c10	d10
Sheet2

Results:

Dante Amor
	A	B	C	D	E
1
2	some	b2	c2	d2
3	some	b5	c5	d5
4	some	b7	c7	d7
5	some	b9	c9	d9
6
Sheet3

How about:

Sub Copy_Rows()
  With Sheets("Sheet2")
    .Range("A1").AutoFilter 1, Sheets("Sheet1").Range("A21")
    .AutoFilter.Range.Offset(1).EntireRow.Copy Sheets("Sheet3").Range("A2")
    .ShowAllData
  End With
End Sub

 

[zerlotus]

Hello alansidman & DanteAmor,

Both of your solutions worked effortleslly great.

Thank you both, I found the two solutions really useful.

 

[DanteAmor]

Im glad to help you, thanks for the feedback.