dpaton05
Well-known Member
- Joined
- Aug 14, 2018
- Messages
- 2,392
- Office Version
- 365
- 2016
- Platform
- Windows
I have sheets for every month of the year. All these sheets are the same and have the following format.
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.
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:
The code allows for a request number and a purchase order number to be entered. After the PO# is entered
This is what I want to be able to do
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
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:
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