i want to do the following

In sheet1 ,if A1="ABC" transfer entire row1 data to sheet2
or if A1="DEF" transfer entire row1 data to sheet 3

similarly do for A2,A3,A4,A5...... cell of sheet1 and transfer data to
sheet 2 or sheet 3

thanks in advance

Sub transferrowif()
For Each c In sheets("sheet1").Range("a1:a21")

If LCase(c) = "abc" Then
With Sheets("sheet2")
dlr = .Cells(Rows.Count, 1).End(xlUp).Row + 1
c.EntireRow.Copy .Cells(dlr, 1)
End With

ElseIf LCase(c) = "def" Then
With Sheets("sheet3")
dlr = .Cells(Rows.Count, 1).End(xlUp).Row + 1
c.EntireRow.Copy .Cells(dlr, 1)
End With
End If
Next c

End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"aditya" wrote in message
...
i want to do the following

In sheet1 ,if A1="ABC" transfer entire row1 data to sheet2
or if A1="DEF" transfer entire row1 data to sheet 3

similarly do for A2,A3,A4,A5...... cell of sheet1 and transfer data to
sheet 2 or sheet 3

thanks in advance

or use datafilterautofilterfilter and then copy the entire block to the
desired sheet.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Sub transferrowif()
For Each c In sheets("sheet1").Range("a1:a21")

If LCase(c) = "abc" Then
With Sheets("sheet2")
dlr = .Cells(Rows.Count, 1).End(xlUp).Row + 1
c.EntireRow.Copy .Cells(dlr, 1)
End With

ElseIf LCase(c) = "def" Then
With Sheets("sheet3")
dlr = .Cells(Rows.Count, 1).End(xlUp).Row + 1
c.EntireRow.Copy .Cells(dlr, 1)
End With
End If
Next c

End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"aditya" wrote in message
...
i want to do the following

In sheet1 ,if A1="ABC" transfer entire row1 data to sheet2
or if A1="DEF" transfer entire row1 data to sheet 3

similarly do for A2,A3,A4,A5...... cell of sheet1 and transfer data to
sheet 2 or sheet 3

thanks in advance