Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Macro
You may have to change the sheet names in the code, but the basic premise is
he Sub newone() Dim RngColF As Range Dim i As Range Dim Dest As Range Sheets("Sheet1").Select Set RngColF = Range("H1", Range("H" & Rows.Count).End(xlUp)) With Sheets("Sheet2") Set Dest = .Range("A1") End With For Each i In RngColF If i.Value = "Completed" Then i.EntireRow.Copy Dest Set Dest = Dest.Offset(1) End If Next i End Sub Regards, Ryan--- -- RyGuy "Brian" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Macro
Thank you so much. It works great.
"ryguy7272" wrote: You may have to change the sheet names in the code, but the basic premise is he Sub newone() Dim RngColF As Range Dim i As Range Dim Dest As Range Sheets("Sheet1").Select Set RngColF = Range("H1", Range("H" & Rows.Count).End(xlUp)) With Sheets("Sheet2") Set Dest = .Range("A1") End With For Each i In RngColF If i.Value = "Completed" Then i.EntireRow.Copy Dest Set Dest = Dest.Offset(1) End If Next i End Sub Regards, Ryan--- -- RyGuy "Brian" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Macro
Ha!! Glad to hear it worked out for you. Please click the 'Yes' button if
this post was helpful for you. Others may benefit from this information too!! Regards, Ryan--- -- RyGuy "Brian" wrote: Thank you so much. It works great. "ryguy7272" wrote: You may have to change the sheet names in the code, but the basic premise is he Sub newone() Dim RngColF As Range Dim i As Range Dim Dest As Range Sheets("Sheet1").Select Set RngColF = Range("H1", Range("H" & Rows.Count).End(xlUp)) With Sheets("Sheet2") Set Dest = .Range("A1") End With For Each i In RngColF If i.Value = "Completed" Then i.EntireRow.Copy Dest Set Dest = Dest.Offset(1) End If Next i End Sub Regards, Ryan--- -- RyGuy "Brian" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Loop | Excel Discussion (Misc queries) | |||
help with this loop macro | Excel Programming | |||
Loop to run macro each row | Excel Discussion (Misc queries) | |||
Do until loop with use of another macro in loop | Excel Programming | |||
how to put a loop in a macro? | New Users to Excel |