Macro to find and paste rows

[Guest]

I need a macro to find every timt "Revoked" is found in Column C, cut the
row, and paste it into Sheet2 of current worksheet. I need it to loop
through and cut all rows with "Revoked" and then paste so all are available
in Sheet 2. This below only does the action once, but I dont know how to make
it loop and stop when no more Revokes are found.
THANK YOU!

Cells.Find(What:="Revoked", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Copy
End With
Sheets("Sheet2").Select
ActiveCell.Offset(-6, -1).Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
End Sub

 

[Guest]

As an alternative to your macro, consider using an AutoFilter. You can use
the filter to display only the rows with Revoked in column C. Then you can
copy/paste all the required rows with only one copy/paste.

 

[Guest]

Gary - This didnt quite work - I need the records removed from sheet 1 and
pasted to sheet. The cut with the autofilter takes all the hidden rows in
the autofilter too.

Any other suggestions are appreciated!!!
====================================================

 

[Gord Dibben]

With filter enabled..........F5>Special>Visible cells only>OK

Copy(you're not allowed to Cut) then switch to target sheet and Paste. Do not
hit ENTER

Switch back to source sheet and Edit>Delete Row

The filtered data will be gone from source sheet.


Gord Dibben MS Excel MVP

 

[Guest]

Thank you! This in combo with what Gary gave me works perfectly! Also, in
the meantime I found some code from Gary's response to Wanna Learn on 1/31/07
that also nicely removed the rows from the source sheet.

 

[Gord Dibben]

On second thought, I don't like this.

It deletes the header row also.


Gord