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

clivebillowes

New Member
Joined
Jul 5, 2019
Messages
4
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.:mad:

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 a moderator:

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,147
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:

clivebillowes

New Member
Joined
Jul 5, 2019
Messages
4
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.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,147
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:

clivebillowes

New Member
Joined
Jul 5, 2019
Messages
4
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/kkb5afjwjv2tnnl/Clive Contracts 2019.xlsm?dl=0
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,147
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:

Watch MrExcel Video

Forum statistics

Threads
1,101,851
Messages
5,483,306
Members
407,393
Latest member
GeorgeBrown

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top