How to make the VBA UDF function run in all worksheets?

[Young_Money]

Hi guys.. My question today is how do I get my UDF to run in all worksheets of my excel file?

Here is the code I have that runs on one worksheet

Function Custom_Lookup(ByVal Client_Initials As String) As String
Dim r
For Each r In Range("B:B")
If r = Client_Initials Then
Custom_Lookup = r.Offset(0, 1)
Exit Function
End If
Next

Custom_Lookup = "Not Found"

End Function

It runs the worksheet NewContractOrder
However, I want it to be able to run on a worksheet top opportunity and opportunity as well.
All 3 worksheets have the same format, so the function will work correctly on all of them..but my question is how to get the function to work in all 3 excel sheets instead of just NewContractOrder.

Any help would be appreciated!

 

[mvptomlinson]

If you place the custom function in a standard module (not a worksheet or ThisWorkbook module) then it should be available on any sheet in the workbook.

 

[Young_Money]

I see.. how would I go about doing that?
I know to create a module I go to macro then VBE then right click modules and click create a new module.. but how do I make a standard module?

Sorry I am really new to VBA..

 

[Young_Money]

Never mind.. I googled it!
Thanks!

 

[Young_Money]

Oh I see..
I think I did make a standard module to run this UDF
However, when I delete the first sheet, the customlookup function does not work anymore..
Do you know why?

I want the function to be able to run independently..
so it will look at the initials row for any worksheet and print out the box to the right of it

 

[Young_Money]

OH nevermind it does work haha..
i derped myself
thanks for your time!