Hello, I am new to writing VBA code and am not sure where to start re writing
code to do the following with excel:

a) Find the first instance of "completed" within the status field within
the active exceptions worksheet.
b) select the row that contains this status
c) copy or cut the row and insert copied cells into row 1 of the completed
worksheet (shifting the current data down within the completed worksheet)
d) delete the completed record that was selected and copied above.
e) loop until all compled status records have been moved to the completed
worksheet.

If anyone could help me with this one I would be very appreciative!
--
SherryW

Let's see if we can get you started in learning how to do this.
1. record a macro while doing one manually to see what is happening.
2. Look in the vba help index for FINDNEXT and incorporate your procedure
into that.
3. Post back with specific questions.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"SherryW" wrote in message
...
Hello, I am new to writing VBA code and am not sure where to start re
writing
code to do the following with excel:

a) Find the first instance of "completed" within the status field within
the active exceptions worksheet.
b) select the row that contains this status
c) copy or cut the row and insert copied cells into row 1 of the completed
worksheet (shifting the current data down within the completed worksheet)
d) delete the completed record that was selected and copied above.
e) loop until all compled status records have been moved to the completed
worksheet.

If anyone could help me with this one I would be very appreciative!
--
SherryW

Great suggestion! I did try this but with a twist which didn't work well.
Currently, there is a filter on the status field and what I was doing when
recording the macro was first filtering the field by completed and then
trying to move all of the records over to the completed worksheet. This did
not work as expected...

What I should be doing is what you suggested, recording the macro to find
the first instance WITHOUT doing the filter first and then working from there.

Thanks, I needed a different perspective to get me started. If, I run into
difficulties I will post the specifics.
--
SherryW


"Don Guillett" wrote:

Let's see if we can get you started in learning how to do this.
1. record a macro while doing one manually to see what is happening.
2. Look in the vba help index for FINDNEXT and incorporate your procedure
into that.
3. Post back with specific questions.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"SherryW" wrote in message
...
Hello, I am new to writing VBA code and am not sure where to start re
writing
code to do the following with excel:

a) Find the first instance of "completed" within the status field within
the active exceptions worksheet.
b) select the row that contains this status
c) copy or cut the row and insert copied cells into row 1 of the completed
worksheet (shifting the current data down within the completed worksheet)
d) delete the completed record that was selected and copied above.
e) loop until all compled status records have been moved to the completed
worksheet.

If anyone could help me with this one I would be very appreciative!
--
SherryW

One way:

Public Sub MoveCompleted()
Const nStatusCol As Long = 6 'Change to suit
Dim rCell As Range
Dim rCopy As Range
Application.ScreenUpdating = False
With Sheets("Sheet1")
.Rows(1).Insert
.Cells(1, nStatusCol).Value = "Temp"
.Cells(2, 1).CurrentRegion.AutoFilter _
Field:=nStatusCol, _
Criteria1:="Completed"
On Error Resume Next
Set rCopy = .Range(.Cells(2, 1), .Cells(.Rows.Count, _
1).End(xlUp)).SpecialCells(xlCellTypeVisible)
If rCopy.Cells(1).Row = 1 Then Set rCopy = Nothing
On Error GoTo 0
.Rows(1).Delete
End With
If Not rCopy Is Nothing Then
With Sheets("Sheet2")
For Each rCell In rCopy
.Rows(1).Insert
rCell.EntireRow.Copy .Cells(1, 1)
Next rCell
End With
rCopy.EntireRow.Delete
End If
Application.ScreenUpdating = True
End Sub


In article ,
SherryW wrote:

Hello, I am new to writing VBA code and am not sure where to start re writing
code to do the following with excel:

a) Find the first instance of "completed" within the status field within
the active exceptions worksheet.
b) select the row that contains this status
c) copy or cut the row and insert copied cells into row 1 of the completed
worksheet (shifting the current data down within the completed worksheet)
d) delete the completed record that was selected and copied above.
e) loop until all compled status records have been moved to the completed
worksheet.

If anyone could help me with this one I would be very appreciative!

Don,

I did as suggested and recorded the macro

steps taken when recording the macro we

select the status column
click on edit, find
typed in completed in the find what box
clicked on find next
selected the first row that was populated with completed
clicked on edit\cut
click on the completed worsheet to activate sheet
clicked on row 2 and clicked on edit, insert cut cells
clicked on the active exceptions worksheet to activate and deleted the now
blank line due to the cut done above.
Stop recording.

The code that was created from this recording was:
Columns("E:E").Select
Selection.Find(What:="completed", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Rows("5:5").Select
Selection.Cut
Sheets("Completed Requests").Select
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Sheets("Active Requests").Select
Rows("5:5").Select
Selection.Delete Shift:=xlUp
End Sub

Now, the following line is coded into the macro: Rows("5:5").Select and
this seems to indicate that Row("5.5") is always the row to cut which, it
won't be...is there a way to code to say select the row no matter the
position that the completed value is in?



--
SherryW


"Don Guillett" wrote:

Let's see if we can get you started in learning how to do this.
1. record a macro while doing one manually to see what is happening.
2. Look in the vba help index for FINDNEXT and incorporate your procedure
into that.
3. Post back with specific questions.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"SherryW" wrote in message
...
Hello, I am new to writing VBA code and am not sure where to start re
writing
code to do the following with excel:

a) Find the first instance of "completed" within the status field within
the active exceptions worksheet.
b) select the row that contains this status
c) copy or cut the row and insert copied cells into row 1 of the completed
worksheet (shifting the current data down within the completed worksheet)
d) delete the completed record that was selected and copied above.
e) loop until all compled status records have been moved to the completed
worksheet.

If anyone could help me with this one I would be very appreciative!
--
SherryW

A re-read suggests using the autofilter already suggested but something like
this would also work.
Example from the vba help index for FINDNEXT modified

Sub cutem()
On Error Resume Next
With Range("a2:a" & Cells(Rows.Count, "a").End(xlUp).Row)
Set c = .Find("cc", LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Cut
Sheets("sheet2").Cells(2, 1).Insert shift:=xlDown
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
Columns(1).SpecialCells(xlBlanks).EntireRow.Delete
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"SherryW" wrote in message
...
Don,

I did as suggested and recorded the macro

steps taken when recording the macro we

select the status column
click on edit, find
typed in completed in the find what box
clicked on find next
selected the first row that was populated with completed
clicked on edit\cut
click on the completed worsheet to activate sheet
clicked on row 2 and clicked on edit, insert cut cells
clicked on the active exceptions worksheet to activate and deleted the now
blank line due to the cut done above.
Stop recording.

The code that was created from this recording was:
Columns("E:E").Select
Selection.Find(What:="completed", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Rows("5:5").Select
Selection.Cut
Sheets("Completed Requests").Select
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Sheets("Active Requests").Select
Rows("5:5").Select
Selection.Delete Shift:=xlUp
End Sub

Now, the following line is coded into the macro: Rows("5:5").Select and
this seems to indicate that Row("5.5") is always the row to cut which, it
won't be...is there a way to code to say select the row no matter the
position that the completed value is in?



--
SherryW


"Don Guillett" wrote:

Let's see if we can get you started in learning how to do this.
1. record a macro while doing one manually to see what is happening.
2. Look in the vba help index for FINDNEXT and incorporate your procedure
into that.
3. Post back with specific questions.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"SherryW" wrote in message
...
Hello, I am new to writing VBA code and am not sure where to start re
writing
code to do the following with excel:

a) Find the first instance of "completed" within the status field
within
the active exceptions worksheet.
b) select the row that contains this status
c) copy or cut the row and insert copied cells into row 1 of the
completed
worksheet (shifting the current data down within the completed
worksheet)
d) delete the completed record that was selected and copied above.
e) loop until all compled status records have been moved to the
completed
worksheet.

If anyone could help me with this one I would be very appreciative!
--
SherryW

Thanks guys, got it and I learned valuable tips. I will be able to utilize
this type of code in other applications.
--
SherryW


"JE McGimpsey" wrote:

One way:

Public Sub MoveCompleted()
Const nStatusCol As Long = 6 'Change to suit
Dim rCell As Range
Dim rCopy As Range
Application.ScreenUpdating = False
With Sheets("Sheet1")
.Rows(1).Insert
.Cells(1, nStatusCol).Value = "Temp"
.Cells(2, 1).CurrentRegion.AutoFilter _
Field:=nStatusCol, _
Criteria1:="Completed"
On Error Resume Next
Set rCopy = .Range(.Cells(2, 1), .Cells(.Rows.Count, _
1).End(xlUp)).SpecialCells(xlCellTypeVisible)
If rCopy.Cells(1).Row = 1 Then Set rCopy = Nothing
On Error GoTo 0
.Rows(1).Delete
End With
If Not rCopy Is Nothing Then
With Sheets("Sheet2")
For Each rCell In rCopy
.Rows(1).Insert
rCell.EntireRow.Copy .Cells(1, 1)
Next rCell
End With
rCopy.EntireRow.Delete
End If
Application.ScreenUpdating = True
End Sub


In article ,
SherryW wrote:

Hello, I am new to writing VBA code and am not sure where to start re writing
code to do the following with excel:

a) Find the first instance of "completed" within the status field within
the active exceptions worksheet.
b) select the row that contains this status
c) copy or cut the row and insert copied cells into row 1 of the completed
worksheet (shifting the current data down within the completed worksheet)
d) delete the completed record that was selected and copied above.
e) loop until all compled status records have been moved to the completed
worksheet.

If anyone could help me with this one I would be very appreciative!