Thread: Loop Macro
View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Brian Brian is offline
external usenet poster
 
Posts: 683
Default Loop Macro

I have the following marco set up and I would like to have it loop searching
for the work "Completed" in column "H" until all are moved to the next
worksheet. Can anyone help?

Sub Active()
Application.ScreenUpdating = False

Columns("H:H").Select
Cells.Find(What:="completed", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Dim sourceRange As Range
Dim destrange As Range
Dim Lr As Long
Lr = LastRow(Sheets("Completed")) + 1
Set sourceRange = ActiveCell.EntireRow
Set destrange = Sheets("Completed").Rows(Lr)
sourceRange.Copy destrange
sourceRange.EntireRow.Delete

Sheets("Active").Select
Range("A2").Select

Application.ScreenUpdating = True

End Sub