+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    01-31-2008
    Posts
    2

    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.

  2. #2
    Registered User
    Join Date
    12-03-2007
    Location
    newport uk
    Posts
    12
    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

  3. #3
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,140
    Hi,
    Try this this out,
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    01-31-2008
    Posts
    2
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1