+ Reply to Thread
Results 1 to 2 of 2

Moving an entire row to new worksheet based on variable cell value.

Hybrid View

  1. #1
    Registered User
    Join Date
    10-24-2012
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2010
    Posts
    2

    Moving an entire row to new worksheet based on variable cell value.

    So hooray for my first post! Now on to the serious business...

    I have read many posts relating to this for moving an entire row based on text of one cell, but only for one word. I am havin no luck finding the script to move it to a different sheet based on different words (in the same cell).

    I have an excel "tacking log" for my clients' cases. It contains several columns:

    A - County
    B - Application number (alphanumeric)
    C - Record number (numeric)
    D - Program type (text only)
    E - Date received (MM/DD/YYYY format)
    F - Days active (using formula =TODAY()-E2) *example formula
    G - Client Name (text only)
    H - Pending Due (MM/DD/YYYY format)
    I - Reject day (same format, using formula H2+2) *example formula
    J - Status (text, variable)
    K - Comments (text only)

    In column J, there are are 6 possible status options ( "" [blank], Pending, Rejected, Authorized, Returned, Issues).

    There are 6 worksheets in the workbook: Received [for blank column J], pending, rejected, authorized, returned, issues.

    What I would like is to have it move the entire row A:K to the corresponding sheet based on column J. After the row is moved, I would like to have the original row deleted and rows shifted up.

    I have searched high and low to find out how to stack these instances, but have yet been unsuccessful. Hopefully there is someone out there who can help. Thank you all in advance. I hope I have explained this well enough.

    Cheers,
    Adam

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Moving an entire row to new worksheet based on variable cell value.

    Maybe:

    Sub aovermille()
    Dim lr As Long
    Dim rcell As Range
    
    lr = Cells(Rows.Count, 10).End(xlUp).Row
    
    For Each rcell In Range("J2:J" & lr)
    
        Select Case rcell.Value
        
            Case Is = ""
            
                rcell.EntireRow.Copy Sheets("Received").Range("A" & Rows.Count).End(3)(2)
                
                rcell.EntireRow.Delete shift:=xlUp
                
            Case Is = "Pending"
            
                rcell.EntireRow.Copy Sheets("Pending").Range("A" & Rows.Count).End(3)(2)
                
                rcell.EntireRow.Delete shift:=xlUp
                
            Case Is = "Rejected"
            
                rcell.EntireRow.Copy Sheets("Rejected").Range("A" & Rows.Count).End(3)(2)
                
                rcell.EntireRow.Delete shift:=xlUp
                
            Case Is = "Authorized"
            
                rcell.EntireRow.Copy Sheets("Authorized").Range("A" & Rows.Count).End(3)(2)
                
                rcell.EntireRow.Delete shift:=xlUp
                
            Case Is = "Returned"
            
                rcell.EntireRow.Copy Sheets("Returned").Range("A" & Rows.Count).End(3)(2)
                
                rcell.EntireRow.Delete shift:=xlUp
                
            Case Is = "Issues"
            
                rcell.EntireRow.Copy Sheets("Issues").Range("A" & Rows.Count).End(3)(2)
                
                rcell.EntireRow.Delete shift:=xlUp
                
            End Select
    
    
    Next rcell
    
                
    End Sub
    Untested try on a copy first.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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