Copy rows if

K

Kashyap

I was trying to copy rows from Sheet2 to Sheet3 if Sheet2 A:A.value= Sheet1
B1.value
 
J

Jacob Skaria

Dear Kashya

Try the below and feedback (untested)

Sub MyMacro()
Dim lngRow As Long
Dim lngLastRow As Long
Dim lngNewRow As Long
Dim varTemp As Variant

lngLastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
lngNewRow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = 2 To lngLastRow
If Sheets("Sheet1").Range("A" & lngRow) = Sheets("Sheet1").Range("B1") Then
varTemp = Sheets("Sheet1").Range(lngRow & ":" & lngRow)
Sheets(2).Range(lngRow & ":" & lngRow) = varTemp
lngNewRow = lngNewRow + 1
End If
Next

End Sub
 
J

Jacob Skaria

Modified to suit your requirement Sheet2 to Sheet3 referring Sheet1 B1....

Sub MyMacro()
Dim lngRow As Long
Dim lngLastRow As Long
Dim lngNewRow As Long
Dim varTemp As Variant

lngLastRow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
lngNewRow = Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = 1 To lngLastRow
If Sheets("Sheet2").Range("A" & lngRow) = Sheets("Sheet1").Range("B1") Then
varTemp = Sheets("Sheet2").Range(lngRow & ":" & lngRow)
Sheets(3).Range(lngNewRow & ":" & lngNewRow) = varTemp
lngNewRow = lngNewRow + 1
End If
Next

End Sub
 
J

Jacob Skaria

I have tried with the below data in a new workbook. Since I havent refered
the workbook name it works with the active workbook. Try the below

Sheet1 B1 = 1
Sheet2 Col A and Col B with below data

1 a
2 b
1 a
2 b
1 a

and Sheet3 blank


If this post helps click Yes
 
K

Kashyap

Sorry Jocob, there was a slight error while editing.. Its working fine now..
:) can we also copy rows from Sheet2 to Sheet3 if

Sheet2 A:A.value>= Sheet1 B1.value & Sheet2 A:A.value<= Sheet1 B2.value ?
 
J

Jacob Skaria

Hi Kashya

Try and feedback..

Sub MyMacro()
Dim lngRow As Long
Dim lngLastRow As Long
Dim lngNewRow As Long
Dim varTemp As Variant

lngLastRow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
lngNewRow = Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row + 1
For lngRow = 1 To lngLastRow
If Sheets("Sheet2").Range("A" & lngRow) >= Sheets("Sheet1").Range("B1") And _
Sheets("Sheet2").Range("A" & lngRow) <= Sheets("Sheet1").Range("B2") Then
varTemp = Sheets("Sheet2").Range(lngRow & ":" & lngRow)
Sheets(3).Range(lngNewRow & ":" & lngNewRow) = varTemp
lngNewRow = lngNewRow + 1
End If
Next

End Sub


If this post helps click Yes
 
Top