Updating code to reflect entries are now made from one location

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,007
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
VBA 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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,007
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Actually, I figured out something else, don't worry about this question.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,007
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
My code stopped working.

This is my code
VBA 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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,364
Messages
5,595,722
Members
414,013
Latest member
tnobbs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top