HOw i can search a particular account and cut the entire row and paste in another

[harikrishna]

Hello All,

HOw i can search a particular account (irrestpective of how many times it repeats)and cut the entire row and paste in another sheet 2.
Eg: some cells Sheet 1 of spread sheet contains as
191850
191850
191258
191300
191850
191258

Now the macro should find the account 191850 and cut the entire row and paste in sheet 2, As teh account is repeated 3times all teh tree rows should cut and paste in sheet 2 one belwo to other. Similarly same case with the A/c # 191258.As this account is repeated twice the two rows shold be cut and pasted in sheet in continution to teh earlier account191850.

In Sheet 2 the out put looks as below....
191580
191850
191850
191258
191258

 

[Yahya]

Try this
this is your data
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">Search</td><td style="text-align: right;;">191850</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">AC #</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;color: #FF00FF;;">191850</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;text-align: right;color: #FF00FF;;">191850</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;text-align: right;color: #008000;;">191258</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="font-weight: bold;text-align: right;;">191300</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="font-weight: bold;text-align: right;color: #FF00FF;;">191850</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="font-weight: bold;text-align: right;color: #008000;;">191258</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

in C1 write account number then run the code

Sub Test()
Dim LR As Long, ws1 As Worksheet, ws2 As Worksheet, LR2 As Long

Set ws1 = Sheets("sheet1")
Set ws2 = Sheets("sheet2")
LR = ws1.Range("A" & Rows.Count).End(xlUp).Row

For i = LR To 5 Step -1
    With ws1
    If .Cells(i, 1).Value = ws1.Range("c1").Value Then
    LR2 = ws2.Range("A" & Rows.Count).End(xlUp).Row + 1
        .Cells(i, 1).Cut ws2.Range("A" & LR2)
    End If
    End With
Next i


End Sub