moving row of data from one sheet to another

[markyc]

Hi all

Please can you help I have a spreadsheet of information showing outstanding projects.

Once I have completed the project I currently have to cut and paste the row of information from, 'outstanding' sheet to 'completed' sheet.

I have entered a drop down in column F with the option Outstanding / Completed.

What I would like to do is create a macro that goes down my list and moves all the completed to the other sheet.

Any help will be excellent

[Killian]

There is an opttional argument to the copy and cut methods that specifies the destination. You might also need to include a function to find the next available row on the completed sheet. You should be able to piece together the code from this thread - post back if you run into problems!

[markyc]

Killan

I have looked at your code and this is quite advanced for my knowledge.
I have created the below code but this moves all lines of data until it gets to withdrawn then it stops

Please can you have a look to see where I have gone wrong

Sub ClearWithdrawns()
Dim lastrow As Long, r As Long
lastrow = ActiveSheet.UsedRange.Rows.Count

For r = lastrow To 2 Step -1
If UCase(Cells(r, 8).Value) = "WITHDRAWN" Then Application.Run "moveinformation"
Next r
End Sub


Sub moveinformation()

ActiveCell.Rows("1:1").EntireRow.Select
Selection.Cut
Sheets("MCC").Select
Range("a2").Select
ActiveSheet.Paste
Sheets("Notices").Select
Selection.Delete shift:=xlUp
Application.Run "clearwithdrawns"
End Sub

Thanks

Markyc

[Killian]

I'm not sure why you Run "clearwithdrawns" in "moveinformation" - doesn't that just make it recursive? Also, I can't see how the workbook structure is, (sheets "Notices" & "MCC"?)

So here's some example code: It runs taking the ActiveSheet, looks for rows that have "WITHDRAWN" in column H and moves them to a sheet named "Notices"

I've followed your basic method - working up from the bottom of the range with a For...Next loop using Step -1, checking for the keyword copying then deleting if it's found.
I've kept it all in one main routine, except for a function I call a couple of times that gets the next available row on a worksheet.

Hopefully that's a bit clearer !?[VBA]Sub Main()

Dim i As Long

For i = TargetRow(ActiveSheet, 1) - 1 To 2 Step -1
If UCase(ActiveSheet.Cells(i, 8).Value) = "WITHDRAWN" Then
ActiveSheet.Rows(i).Copy Sheets("Notices").Cells(TargetRow(Sheets("Notices"), 1), 1)
ActiveSheet.Rows(i).Delete
End If
Next i

End Sub

Function TargetRow(ByRef ws As Worksheet, ByVal col As Long) As Long
'returns index of first empty row from bottom of sheet
'requires worksheet object and column index
TargetRow = ws.Cells(Rows.Count, col).End(xlUp).Row
If IsEmpty(ws.Cells(TargetRow, col)) Then
'in this case the column is empty
TargetRow = 1
Else
TargetRow = TargetRow + 1
End If
End Function[/VBA]