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 entered

Range("G1").Value = "Date"

at the bottom worksheet_change event on the Cancellations module, which seems to have done the trick.

 

[dpaton05]

I forgot to mention two things. The entries are not all divided into the months that they appear to be in. Anything up and including the 25th of a month goes in that monthly sheet, but anything after the 25th goes in the following month sheet.

For instance, an entry of the 30/5/20 will not be in the May sheet but in June sheet. I think this might have an impact as I tried to cancel a entry that was after the 25th of the month but seeing as though that was on the following monthly sheet, nothing happened. I am guessing that it looked for it in the monthly sheet of it's date and as it wasn't there, nothing happened. Would the code need updating to be able to cancel quotes that are after the 25th of a month?

The other thing is that quotes may span across a period. I want to in the future be able to enter a range of dates, for instance, one quote may be 20/5/20-25/5/20, with a request number of 10200. The documents are financial year documents and they may span over more than 1 financial year so I was thinking that a possible solution for the issues, after the 25th of the month going to be following month and quotes spanning over separate financial years could be searching through the entire document, looking for the date and request number.

I am not sure how I could do this, could you give me some ideas please?

 

[dpaton05]

I don't mind having to open a separate document apart from the one I am working on, to purge cancelled quotes out of that also, so my solution I thought of would be adequate. Can you please help me code it but?

 

[dpaton05]

Sorry to be a real pain but I am having trouble trying to understand your code. Could you tell me the code I would attach to a command button to run procedure please?

 

[vcoolio]

Just going back to your post #50:-

I buggered up this line with too many quotation marks :-

sh.Range("F1", "H1").ClearContents

so change it to

sh.Range("F1,H1").ClearContents

and it should leave the word Date in G1. Hence remove your addition from the Worksheet_Change code.

........and back to post #52:-

If I have understood your query:

If you have, for example, entries from the 26th of June to the 30th of June, these would be actually placed into the July sheet. Correct? Hence, you'd like these June entries that are in the July sheet still to be transferred to the "Cancellations" sheet and removed from the July sheet once you make the appropriate Date and Req.# entries in the "Cancellations" sheet. Correct?
If I'm correct on both assumptions, then just make the appropriate entries in the "Cancellations" sheet and your wish will be granted. There's no need to alter the code.

 

[vcoolio]

..............and post #52 again:-

The other thing is that  quotes may span across a period. I want to in the future be able to enter a range of dates, for instance, one quote may be 20/5/20-25/5/20, with a request number of 10200. The documents are financial year documents and they may span over more than 1 financial year so I was thinking that a possible solution for the issues, after the 25th of the month going to be following month and quotes spanning over separate financial years could be searching through the entire document, looking for the date and request number.

See how post #55 works for you first then let me know.

 

[dpaton05]

Those suggestions seem to have got it working thanks.

The only thing now is that there may be more than 1 instance of a date and a request number. I had my understanding wrong in when I said there would only be one instance of a date and a request number but there can be multiple.

At the moment, cancelled quotes transfer to the cancellations sheet if there is only one instance but if there are multiple, nothing happens when I put the date in H1 of the cancellations sheet.

 

[vcoolio]

Hello Dp,

The code doesn't need alteration for that.
Again, I've just tested it to ensure that this works and it does. I placed around a dozen entries in the July and August sheets with about half having the same dates and Req#. I also mixed the dates up between July and August (some July dates in August, some August dates in July). In all instances, all relevant data was transferred to the Cancellations sheet.

Cheerio,
vcoolio.

 

[dpaton05]

I think I found the problem I was having. I was entering 3/6/20 into H1 on the cancellations sheet, so it was looking for that string in the monthly sheets but in the monthly sheets the date was recorded as 03/06/2020, so I entered the full date and it worked fine as you described it.

Thank you
Dave

 

[vcoolio]

Hello Dp,

To save you having to type the date in full, just re-format H1 the same as your source cells. In your sample, I made sure that all dates in all sheets were formatted the same and had assumed that your actual workbook was as well.

Cheerio,
vcoolio.