Macro to cut rows based on NONBLANK cell and append to list in separate worksheet - x

Hi,

I'm new here, so please keep me right - i've read the forum rules, but if I infringe in any way, please let me know

Also, I should say that this is a cross post - it's also on the Mr Excel message board at http://www.mrexcel.com/forum/showthr...ar#post1475908 - I am hoping to resolve this quickly, so any help is greatly appreciated. I will update the other thread with this URL, and ensure that I update either post if I recieve any replies. Is this ok?!

So, the problem:

I have a worksheet, "Current Week", with a list of all staff in range A8:AA1003.
If a staff member has left in the previous 7 days, a leaving date is entered in column 'N', otherwise it's blank.
I need to remove all records of these 'leavers' (i.e. all records, A:AA, where N is not blank) on a weekly basis and append the records to the bottom of a list in a separate "Leavers Archive" worksheet.

Unfortunately my VBA skills are extremely limited and I don't have the first idea how to achieve this! Any ideas?

Many thanks in advance.

HI
I USE THIS TO MOVE CERTAIN ITEMS FROM ONE SHEET TO ANOTHER
IT SEARCHES FOR WHAT IS ENTERED IN THE INPUT BOX

Please Login or Register  to view this content.

OBVIOUSLY CHANGE THE SHEET AND RANGES TO SUIT
NOTE: NOT SURE WHAT IT WILL BE LIKE WITH DATES, YOU MAY HAVE TO CONVERT DATES TO TEXT


ANYWAY HAVE A LOOK

HOPE IT HELPS

EVEN A LITTLE



JOHN

Hi,



After much pain, I have managed to get this working with the following code:



Sub Archive_Leavers()

Dim LastRowMain As Long
Dim LastRowEntered As Long
Dim i As Long

Application.ScreenUpdating = False

LastRowMain = Worksheets("Current Week").Range("A" & Rows.Count).End(xlUp).Row
With Worksheets("Current Week")
For i = 8 To LastRowMain Step 1
If Cells(i, "n") <> "" Then
LastRowEntered = Worksheets("Leavers Archive").Range("A" & Rows.Count).End(xlUp).Row
Rows(i).Cut Worksheets("Leavers Archive").Range("A" & LastRowEntered + 1)

End If
Next i

End With

Worksheets("Current Week").Select
Application.ScreenUpdating = True

End Sub


Thanks for all the help