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''s Student
gsnu200707


"kayabob" wrote:

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

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!!!
================================================== ==

"Gary''s Student" wrote:

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''s Student
gsnu200707


"kayabob" wrote:

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

With filter enabled..........F5SpecialVisible cells onlyOK

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

Switch back to source sheet and EditDelete Row

The filtered data will be gone from source sheet.


Gord Dibben MS Excel MVP

On Tue, 20 Feb 2007 14:56:08 -0800, kayabob
wrote:

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!!!
================================================= ===

"Gary''s Student" wrote:

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''s Student
gsnu200707


"kayabob" wrote:

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

On second thought, I don't like this.

It deletes the header row also.


Gord

On Tue, 20 Feb 2007 15:20:39 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

With filter enabled..........F5SpecialVisible cells onlyOK

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

Switch back to source sheet and EditDelete Row

The filtered data will be gone from source sheet.


Gord Dibben MS Excel MVP

On Tue, 20 Feb 2007 14:56:08 -0800, kayabob
wrote:

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!!!
================================================ ====

"Gary''s Student" wrote:

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''s Student
gsnu200707


"kayabob" wrote:

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