Copying quote rows from monthly sheets to cancellation sheet if criteria is met

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,784
I have sheets for every month of the year. All these sheets are the same and have the following format.

CSS Work Allocation Sheet.3.xlsm
ABCDEFGHIJKLMNOP
1501 CSS JulyEnter Req # in F1, then a PO # in H1 and the entire spreadsheet will be auto populatedReq #Purchase order #If you enter a Req # and the letter x in the PO #, every PO # against the Req # will be cleared.
2
3DatePurchase order #Req #Child NameServiceRequesting OrganisationCaseworker NamePrice ex. GSTGSTPrice inc. GSTAllocated toDate report receivedDate report sentAllocated byReport sent byReport sent by
4
5
6
7
8
July

As I mentioned. there are sheets for each month of the year, this is just the July sheet and they are all the same. The monthly sheets record quotes and I have another sheet called Cancellations, which is below.



CSS Work Allocation Sheet.3.xlsm
ABCDEFGHIJKLMNOP
1501 CSS CancellationsEnter request number and date to cancelReq #Date
2
3DatePurchase order #Req #Child NameServiceRequesting OrganisationCaseworker NamePrice ex. GSTGSTPrice inc. GSTAllocated toDate report receivedDate report sentAllocated byReport sent byReport sent by
4
5
Cancellations


The pages are very similar and the cancellations sheet is just an area to record quotes that have been cancelled.



I have been helped with code to update every request number in the document with a purchase order number and that code goes in the ThisWorkbook module. The code is as follows:
VBA Code:
Option Explicit
'this is triggered whenever cell H1 is amended in any of the listed sheets
Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
    Dim Req As Range, PO As Range
    Select Case WorksheetFunction.Proper(sh.Name)
        Case "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"
            Set Req = sh.Range("F1")
            Set PO = sh.Range("H1")
            If Not Intersect(Target, PO) Is Nothing Then
                Application.EnableEvents = False
                If PO <> "" And Req <> "" Then Call UpdateEverySheet(Req, PO)
                PO.ClearContents
                Req.ClearContents
                Application.EnableEvents = True
            End If
    End Select
End Sub
'this is called by Sheet_Change and loops through all monthly sheets creating required entries
Private Sub UpdateEverySheet(Req As Range, PO As Range)
    Dim sh, ws As Worksheet, Cel As Range, ReqRng As Range
    If UCase(PO) = "X" Then PO = ""
    For Each sh In Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
        Set ws = Sheets(sh)
        Set ReqRng = ws.Range("C4", ws.Range("C" & Rows.Count).End(xlUp))
        For Each Cel In ReqRng
            If Val(Cel) = Val(Req) Then Cel.Offset(, -1) = PO
        Next Cel
    Next sh
End Sub


The code allows for a request number and a purchase order number to be entered. After the PO# is entered
  • Both cells are cleared
  • Every instance of the request number within the document is updated so that quote with that request number, gets the PO# entered for the quote.

This is what I want to be able to do
  • Enter a request number and a date in F1 and H1 of the Cancellations sheet
  • After entering both I need the quote that matches the request number and date that I have just entered in F1 and H1 to be found in the workbook
  • When found I need it moved from the sheet where it is to the cancellations sheet.
  • Entries below where it quote row was moved from, need to be moved up one row to fill the gap where it was.
  • As with feature to insert the PO# from a given request number, I need F1 and H1 cleared after the row has been moved

I tried to look at the code and I thought I might be able to work it out myself but I can't work it out.

Can someone help me please with the code to make this happen as I don't know how to code it?

Thanks
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,784
I think I can remember changing that code reference from f1/h1 to b25/b27 when I was at work today. I will upload another version of the workbook when I am next at work, which is Friday.

I know you didn't write that code that won't work, I was trying to write some code but I got it all wrong.

With the code in post 68, the reason I took out the select case with all of the monthly sheets was that I thought since, now the data is only coming from one sheet, you only need to reference that sheet.
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,002
Hello Dp,

In the latest sample you have supplied, you have the destination sheet variable 'sh' assigned the value of Sheets("Totals") hence, instead of the data from the monthly source sheets being transferred to the "Cancellations" sheet, it is being transferred to the input sheet "Totals" and literally getting lost amongst the data that is being used for your chart.
Excel won't allow the same variable for two worksheets so you need to declare another variable for the destination sheet. Following is the code again amended to show the change:-

VBA Code:
Option Explicit
Sub Test()
        Dim ws As Worksheet, sh As Worksheet, sht As Worksheet
        Set sh = Sheets("Totals")
        Set sht = Sheets("Cancellations")
        Dim Req As String: Req = sh.[B25].Value
        Dim Dt As String: Dt = sh.[B27].Value
        
Application.ScreenUpdating = False
        
        For Each ws In Worksheets
                If ws.Name <> "Cancellations" And ws.Name <> "Totals" Then
                        With ws.[A3].CurrentRegion
                                .AutoFilter 1, Dt
                                .AutoFilter 3, Req
                                .Offset(1).EntireRow.Copy sht.Range("A" & Rows.Count).End(3)(2)
                                .Offset(1).EntireRow.Delete
                                .AutoFilter
                        End With
                End If
        Next ws
        
sh.Range("B25,B27").ClearContents
Application.ScreenUpdating = True

End Sub

You also need to change this line:-

VBA Code:
sh.Range("F1,H1").ClearContents

to
VBA Code:
sh.Range("B25,B27").ClearContents

just as I have done for you in the code above.

You'll note I've added the new variable 'sht' as the destination sheet (Cancellations") as well.

I've tested it in your sample and the code works as it should.

Cheerio,
vcoolio.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,784

ADVERTISEMENT

I just noticed, it is not sorting on the cancellations sheet by date, even though I have the SortCells sub run every time the sheet is changed.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,784
My sort code is working but whenever I enter in a request number in B25 of the totals sheet and a date in B27, the numbers are copied to the cancellations sheet but the cancellations sheet is not sorted.

This is my code on the totals sheet
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("B27")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

Call Test
Call SortCells
End Sub

After I transfer the cancelled job, it is transferred to the cancellations sheet and the sheet is not sorted, I go to the vba editor and run SortCells manually and that sorts the sheet.

Why won't it run after I enter the date in B27?
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,784

ADVERTISEMENT

I put a button on the cancellations sheet which called the SortCells sub and that works.
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,002
Hello Dp,

Why won't it run after I enter the date in B27?

I noticed that, in your SortCells code, you have commented out the With statement (green font):-

VBA Code:
Sub SortCells()
'With Sheets("Cancellations")
    Range("A4", Range("O" & Rows.Count).End(xlDown).Address).Sort _
    Key1:=Range("A4"), Order1:=xlAscending
'End With

End Sub

Hence, the code is not fully qualified in reference to the sheet that it needs to work on. It is now assuming that it needs to work on the "Totals" sheet as it is being called from within the "Totals" sheet module. Remove the apostrophes from the with statement and it should work on the "Cancellations" sheet or, if you are happy with the button on the "Cancellations" sheet, then leave it as is.

It would be a better option to fully qualify the SortCells code as follows:-

VBA Code:
Sub SortCells()

Dim ws As Worksheet: Set ws = Sheets("Cancellations")

    ws.Range("A4", ws.Range("O" & ws.Rows.Count).End(xlDown).Address).Sort _
    Key1:=ws.Range("A4"), Order1:=xlAscending

End Sub

I hope that this helps.

Cheerio,
vcoolio.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,784
Thanks for your help vcoolio. I initially tried to remove the quote marks after I asked the question and now at this stage, the result of what I was getting is what I need.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,519
Messages
5,548,522
Members
410,844
Latest member
Juno49
Top