Macro to find and paste rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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!!!
====================================================
 
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
 
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.
 
Back
Top