Copy rows if

  • Thread starter Thread starter Kashyap
  • Start date Start date
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
 
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
 
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
 
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 ?
 
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
 
Back
Top