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:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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:
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top