Cut and paste multiple rows from 3 different sheets if a column meets criteria
Results 1 to 8 of 8

Thread: Cut and paste multiple rows from 3 different sheets if a column meets criteria
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Location
    Springs South Africa
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Cut and paste multiple rows from 3 different sheets if a column meets criteria

    Hello everyone, I am not a coding fundi, nowhere near one, every time I need Excel to do repetitive work, I record a macro, sometimes more that one to do the job. I now require Excel to do something I just can’t record. I've downloaded code but everyone I try has some or other problem, 2 days of trying and I have got nowhere.

    There are 5 sheets “Quoted Client”, “PO Received”, “Progressive Invoicing”, “Completed” and “Failed Quotes”. The first 4 sheets need columns G, H & I to have drop-down list, containing “yes”, “no” & “pending”. All 5 the sheets have row 1 as the headings. There are 26 Columns across the sheets and all sheets have the same headings, the number of rows grows with time, but I have limited then to 5000.

    Information per job quoted gets filled in on the first “Quoted Client” sheet, then if when we receive a PO from the client, we will add the PO info and type “yes” in column G. If the Quote fails, we type “no” into column G.
    “yes” must cause the row to be moved to the next sheet “PO Received”, deleting the row on “Quoted Client” and “no” must move the row to “Failed Quotes” also deleting the row on “Quoted Client”

    When the work on the PO is completed/delivered we type “yes” in column H on the “PO Received” sheet. This will move the row to “Progressive Invoicing” deleting it on “PO Received”.

    In “Progressive Invoicing”, column I, we again have an option of complete invoice or progressive invoicing, here if we type “No” the row must move to “Completed”


    Please if Anyone can assist
    Last edited by Fluff; Jul 5th, 2019 at 10:09 AM. Reason: Removed email address

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,087
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Cut and paste multiple rows from 3 different sheets if a column meets criteria

    Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "Quoted Client" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in column G or I.
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Intersect(Target, Range("G:G,H:H")) Is Nothing Then Exit Sub
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Select Case Target.Column
            Case Is = 7
                Select Case Target.Value
                    Case "yes"
                        Target.EntireRow.Copy Sheets("PO Received").Cells(Sheets("PO Received").Rows.Count, "A").End(xlUp).Offset(1, 0)
                        Target.EntireRow.Delete
                    Case "no"
                        Target.EntireRow.Copy Sheets("Failed Quotes").Cells(Sheets("Failed Quotes").Rows.Count, "A").End(xlUp).Offset(1, 0)
                        Target.EntireRow.Delete
                End Select
            Case Is = 8
                If Target.Value = "yes" Then
                    Target.EntireRow.Copy Sheets("Progressive Invoicing").Cells(Sheets("Progressive Invoicing").Rows.Count, "A").End(xlUp).Offset(1, 0)
                    Target.EntireRow.Delete
                End If
        End Select
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    End Sub
    Repeat the steps with the "Progressive Invoicing" sheet and paste this macro. Make a selection in column I.
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        If Target.Value = "yes" Then
            Target.EntireRow.Copy Sheets("Completed").Cells(Sheets("Completed").Rows.Count, "A").End(xlUp).Offset(1, 0)
            Target.EntireRow.Delete
        End If
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    End Sub
    Make sure you try these macros on a copy of your file.
    Last edited by mumps; Jul 5th, 2019 at 12:09 PM.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    New Member
    Join Date
    Jul 2019
    Location
    Springs South Africa
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cut and paste multiple rows from 3 different sheets if a column meets criteria

    Good Morning Mumps
    Thank you for your assistance, but it is not working, even if I only add code to the first sheet "Quoted Client"
    if I enter 'yes' into G the row is deleted and does not appear on any other sheet
    if I enter 'no' into G the row is deleted and does not appear on any other sheet
    If I enter 'yes into H the row is moved to "Progressive Invoicing" and deleted from "Quoted Client"
    IF I enter 'no' into H nothing changes.

    It could be that I did not explain the flow of rows clearly

    correct flow of rows
    1) all data is entered onto "Quoted Client"
    2) if PO is Received - on "Quoted Client" enter 'yes' in G, moves the row to "PO Received" - If quotation is not accepted (failed) enter 'no' in G, this moves the row to "Failed Quotes"
    3) When work is completed or delivered, on "PO Received" enter 'yes' in H, this moves row to "Progressive Invoicing"
    4) On "progressive Invoicing" sheet, if 'no' is entered into I, the row is moved to "Completed", if 'yes' is entered into I, it will keep the row in "Progressive Invoicing" until invoicing is completed and 'yes' is entered into I.

  4. #4
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,087
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Cut and paste multiple rows from 3 different sheets if a column meets criteria

    I checked the macros and they should work as written. The macro is case sensitive and is expecting "yes" or "no", all lower case, to be entered. If you enter "Yes" or "No", the macros as written won't work. If that is the problem, then try these versions. They are case insensitive.
    Code:
    Option Compare Text
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Intersect(Target, Range("G:G,H:H")) Is Nothing Then Exit Sub
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Select Case Target.Column
            Case Is = 7
                Select Case Target.Value
                    Case "yes"
                        Target.EntireRow.Copy Sheets("PO Received").Cells(Sheets("PO Received").Rows.Count, "A").End(xlUp).Offset(1, 0)
                        Target.EntireRow.Delete
                    Case "no"
                        Target.EntireRow.Copy Sheets("Failed Quotes").Cells(Sheets("Failed Quotes").Rows.Count, "A").End(xlUp).Offset(1, 0)
                        Target.EntireRow.Delete
                End Select
            Case Is = 8
                If Target.Value = "yes" Then
                    Target.EntireRow.Copy Sheets("Progressive Invoicing").Cells(Sheets("Progressive Invoicing").Rows.Count, "A").End(xlUp).Offset(1, 0)
                    Target.EntireRow.Delete
                End If
        End Select
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    End Sub
    
    Option Compare Text
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        If Target.Value = "no" Then
            Target.EntireRow.Copy Sheets("Completed").Cells(Sheets("Completed").Rows.Count, "A").End(xlUp).Offset(1, 0)
            Target.EntireRow.Delete
        End If
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    End Sub
    In the second macro, I have changed "yes" to "no". That was my mistake.
    Last edited by mumps; Jul 8th, 2019 at 08:50 AM.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  5. #5
    New Member
    Join Date
    Jul 2019
    Location
    Springs South Africa
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cut and paste multiple rows from 3 different sheets if a column meets criteria

    HI Mumps
    Thank you for your assistance but it still does not work, I sat with it for most of the day today, trying to tweek it to get it to work, but because I have no idea of what I am doing, that did not work either.
    From Quoted Clients
    both 'yes' and 'no' in G delete the row from the whole workbook
    'yes' in H moves to Progressive Invoicing correct from PO Received
    'no' in H, as it should does nothing
    I did not continue with the other sheets but can see another problem with G & G running the same code
    When a row is moved from Quoted Client to PO Received, there will be a 'yes' in G will this not cause the code to move the row again?
    is there a way I can send you a copy of the spreadsheet? I've added a link to dropbox containing a test copy of the file

    https://www.dropbox.com/s/kkb5afjwjv...2019.xlsm?dl=0

  6. #6
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,087
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Cut and paste multiple rows from 3 different sheets if a column meets criteria

    Click here to download your file. Please note that I have removed all the formulas in column M in all the sheets except "Quoted Clients".

    Code:
    Option Compare Text
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Intersect(Target, Range("G:G,H:H")) Is Nothing Then Exit Sub
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Select Case Target.Column
            Case Is = 7
                Select Case Target.Value
                    Case "yes"
                        Target.EntireRow.Copy Sheets("PO Received").Cells(Sheets("PO Received").Rows.Count, "A").End(xlUp).Offset(1, 0)
                        Target.EntireRow.Delete
                    Case "no"
                        Target.EntireRow.Copy Sheets("Failed Quotes").Cells(Sheets("Failed Quotes").Rows.Count, "A").End(xlUp).Offset(1, 0)
                        Target.EntireRow.Delete
                End Select
            Case Is = 8
                If Target.Value = "yes" Then
                    Target.EntireRow.Copy Sheets("Progressive Invoicing").Cells(Sheets("Progressive Invoicing").Rows.Count, "A").End(xlUp).Offset(1, 0)
                    Target.EntireRow.Delete
                End If
        End Select
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End Sub
    
    Option Compare Text
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        If Target.Value = "no" Then
            Target.EntireRow.Copy Sheets("Completed").Cells(Sheets("Completed").Rows.Count, "A").End(xlUp).Offset(1, 0)
            Target.EntireRow.Delete
        End If
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End Sub
    Last edited by mumps; Jul 9th, 2019 at 09:20 AM.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  7. #7
    New Member
    Join Date
    Jul 2019
    Location
    Springs South Africa
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cut and paste multiple rows from 3 different sheets if a column meets criteria

    THank You Mumps, it is working now. You are a star. Have a great day

  8. #8
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,087
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Cut and paste multiple rows from 3 different sheets if a column meets criteria

    You are very welcome.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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