Updating code to reflect entries are now made from one location

[dpaton05]

I have a spreadsheet that is going to contain information from each month on the year. I did have locations on each monthly sheet that allowed you to enter a request number and a purchase order number. This would search through the entire document to find every instance of the request number and add the purchase order number to that row.

I now want to change it so instead of having the cells to enter both numbers on each monthly sheet, I want to have 2 cells on a sheet called Totals. The cell to enter the request number is B18. The cell to enter the purchase order number is B20. I then want to activate the search and entry by clicking on a button.

I thought that I could read through the code and change it accordingly but I can't.

Can someone please help me update my code so it now runs from pressing the button please?

Here is the code

'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, CancelReq As Range, CancelDate As Range
    Select Case WorksheetFunction.Proper(sh.Name)
        Case "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December", "Cancellations"
            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", "Cancellations")
        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

Thanks

 

[dpaton05]

Actually, I figured out something else, don't worry about this question.

 

[dpaton05]

My code stopped working.

This is my code

'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, CancelReq As Range, CancelDate As Range
    With Sheets("Totals")
        'Case "Totals" ', "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December", "Cancellations"
            Set Req = Range("B18").Value
            Set PO = Range("B20").Value
            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 With
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", "Cancellations")
        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

After you update B18 (request number) and B20 (purchase order number) on the Totals sheet, I need the entire workbook searched to find that request number. The request number is stored in column C on each monthly sheet. Each time it is found, I need the purchase order number that has been entered in B20 to be copied to column B for each row that has the request number.

It was working so I have no idea why it has stopped.

Can anyone help me please?


By the way, I have decided to go with the original method for activating it, not using the button.