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

[dpaton05]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	501 CSS July			Enter Req # in F1, then a PO # in H1 and the entire spreadsheet will be auto populated	Req #		Purchase order #			If you enter a Req # and the letter x in the PO #, every PO # against the Req # will be cleared.
2
3	Date	Purchase order #	Req #	Child Name	Service	Requesting Organisation	Caseworker Name	Price ex. GST	GST	Price inc. GST	Allocated to	Date report received	Date report sent	Allocated by	Report sent by	Report 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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	501 CSS Cancellations			Enter request number and date to cancel	Req #		Date
2
3	Date	Purchase order #	Req #	Child Name	Service	Requesting Organisation	Caseworker Name	Price ex. GST	GST	Price inc. GST	Allocated to	Date report received	Date report sent	Allocated by	Report sent by	Report 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:

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]

I have been opening the file that I sent in post 23 and using that file. I will tell you what I entered.

1. In the July sheet,
   1. Date 7/7/18
   2. Purchase order number 107
2. Went to the cancellations sheet
   1. Entered 107 if F1
   2. Entered 7/7/18 in H1
3. The entry was moved to the Cancellations sheet
4. In the August sheet,
   1. Date 7/8/18
   2. PO number 102
5. Went to the cancellations sheet
   1. Entered the above entries to the Date and PO number fields
6. That entry was moved to the cancellation sheet
7. In the September sheet
   1. Date 7/9/18
   2. PO number 103
8. That entry was moved to the cancellations sheet after entering the above entries in the correct fields
9. In the October sheet
   1. Date 10/7/18
   2. PO number 109
10. Entered the above date and PO number in the correct fields
    1. F1 and H1 were cleared but nothing was moved to that sheet, it was still in the october sheet.

 

[vcoolio]

Why have you got a July date in the October sheet?

 

[dpaton05]

I am using Australian date format, DD/MM/YY

 

[dpaton05]

I think I have made a typo, just let me check it again

 

[dpaton05]

It was just a typo in post 41, I have been entering 7/10/18 into the cancellations sheet but nothing happens when I do that.

 

[dpaton05]

I have been doing some more testing and I enter the same format entries in these sheets and it doesn't work

• October
• November
• December
• January

That is all I have tested so far but I imagine the rest of the sheets probably don't work.

 

[dpaton05]

Just finished testing the rest of the workbook and as I suspected, none of the extra months work.

 

[vcoolio]

Hello Dp,

I've again just tested different months with up to 500 entries per month and all still works seamlessly.
However, I've ensured that Column A in EACH worksheet is formatted as Date (e.g. 15/05/2020) and that H1 in the Cancellations sheet is formatted as Date as well not as Custom.
Ensure that Column A in each sheet is formatted and saved as Date as well as H1 in the Cancellations sheet. You shouldn't then have any issues.

 

[dpaton05]

Thank you!!! THAT IS AWESOME!!!!!

 

[dpaton05]

Where in the code should I update it so the date heading doesn't get deleted out of G1 whenever I enter a date in H1 of the cancellations sheet?