Copy rows if

[Kashyap]

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

 

[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

 

[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

 

[Kashyap]

Hi Jakob, I'm not able to get any result..

 

[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

 

[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 ?

 

[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

 

[Kashyap]

Perfect, Jacob.. Thank you..