Results 1 to 2 of 2

Cut Row to New Sheet, Delete From Previous - based on value in two different columns

Threaded View

gennalaura Cut Row to New Sheet, Delete... 04-13-2017, 11:32 PM
alansidman Re: Cut Row to New Sheet,... 04-14-2017, 12:40 AM
  1. #1
    Registered User
    Join Date
    04-13-2017
    Location
    Scottsdale, AZ
    MS-Off Ver
    Microsoft 2010
    Posts
    1

    Cut Row to New Sheet, Delete From Previous - based on value in two different columns

    Hi All!

    Goal:
    Entire row on Outstanding Issuance & Invoice sheet to be cut and pasted to either the Missing Primary Policies tab or the Completed Accounts tab. Once the entire row is cut and pasted, the original blank row should be deleted.

    This should only happen if the cell in column S for the active row contains a date. I have been trying to use Not IsBlank but got nowhere. Various dates will be entered. If the cell in column S for the active row is blank, nothing should be done.

    If the above criteria is met and the cell contains a date, then the status in Column G determines which tab the row is pasted to. If the cell in Column G = Missing Primary Policy(ies) the row is pasted in the next blank row of the Primary Policies Outstanding tab. If the cell in Column G = Account Complete - All Info Received the row is pasted in the next blank row of the Completes Accounts tab.

    This should happen starting with row 3 until a blank row occurs.

    Current Code:
    The below code is what I am working with now. It partially works, but it does not loop through properly. Anything with Account Complete - All Info Received seems to all be moving, however not all the rows with column G cell value as Missing Primary Policy(ies) seem to be moving. I also need to add the criteria so this only happens is the cell for the active row in Column S is not blank.

    Sub Update()
        Columns.EntireColumn.Hidden = False
        Rows.EntireRow.Hidden = False
        Dim xRg As Range
        Dim xCell As Range
        Dim I As Long
        Dim C As Long
        Dim D As Long
        I = Worksheets("Outstanding Issuance & Invoice").UsedRange.Rows.count
        C = Worksheets("Completed Accounts").UsedRange.Rows.count
        D = Worksheets("Primary Policies Outstanding").UsedRange.Rows.count
        
        If C = 1 Then
           If Application.WorksheetFunction.CountA(Worksheets("Completed Accounts").UsedRange) = 0 Then C = 0
        End If
        Set xRg = Worksheets("Outstanding Issuance & Invoice").Range("G1:G" & I)
        On Error Resume Next
        Application.ScreenUpdating = False
        For Each xCell In xRg
            If CStr(xCell.Value) = "Account Complete - All Info Received" Then
                xCell.EntireRow.Copy Destination:=Worksheets("Completed Accounts").Range("A" & C + 1)
                xCell.EntireRow.Delete
                C = C + 1
            End If
        Next
        Application.ScreenUpdating = True
        
        
        If D = 1 Then
           If Application.WorksheetFunction.CountA(Worksheets("Primary Policies Outstanding").UsedRange) = 0 Then D = 0
        End If
        Set xRg = Worksheets("Outstanding Issuance & Invoice").Range("G1:G" & I)
        On Error Resume Next
        Application.ScreenUpdating = False
        For Each xCell In xRg
            If CStr(xCell.Value) = "Missing Primary Policy(ies)" Then
                xCell.EntireRow.Copy Destination:=Worksheets("Primary Policies Outstanding").Range("A" & D + 1)
                xCell.EntireRow.Delete
                D = D + 1
            End If
        Next
        Application.ScreenUpdating = True
    End Sub
    Last edited by alansidman; 04-14-2017 at 12:40 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Delete Sheet Rows & Columns based on cell value
    By brendangroff in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-10-2016, 05:22 PM
  2. Delete Columns( From One Sheet) based on the header name from a list.
    By v_a_royston in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-29-2015, 08:07 PM
  3. Autofilter, copy from sheet 1 and paste to sheet2, delete previous info on sheet 2
    By leigh12483 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-30-2015, 08:21 PM
  4. VBA Code to delete the previous sum addition saved in summary sheet
    By kumesh10 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-08-2013, 11:54 PM
  5. Replies: 3
    Last Post: 11-06-2012, 11:26 AM
  6. Creating a macro that will delete row based on non matching cell to previous line
    By freidhofr in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-22-2012, 03:47 PM
  7. Macro to add sheet, name it based on previous sheet, and delete a range of cells
    By Moeshell in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-24-2011, 03:26 PM

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