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

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




--
If this post helps click Yes
---------------
Jacob Skaria


"Kashyap" wrote:

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

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


--
If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

> 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
>
>
>
>
> --
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "Kashyap" wrote:
>
> > I was trying to copy rows from Sheet2 to Sheet3 if Sheet2 A:A.value= Sheet1
> > B1.value

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

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
---------------
Jacob Skaria


"Kashyap" wrote:

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

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
---------------
Jacob Skaria


"Kashyap" wrote:

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

Perfect, Jacob.. Thank you..