How To Load 43 Different Macros Into 43 Sheets In VBA

I have a Macro that copys all the cells with a 9 digit number in a range and lists them into col CC

However the way it is written Each macro is stored into its own worksheet. the problem is that i need to do this for all 43 tabs in the workbook which means a lot of copy and past when I build a new workbook.

So is there a way to load 43 diffrent macros into the 43 sheets in vba?

Or is there a way i can make the macros Global? so that they can all be stored in the same place but still work?

The below macro is saved under the workseet "PEBBLES" in vba

Code:

Code:

Sub Pebbles()
Dim objReg As Object, objMatch As Object, objColl As Object
Dim rngWhole As Excel.Range
Dim rngCell As Excel.Range
Dim lngRow As Long: lngRow = 1
Set objReg = CreateObject("vbscript.regexp")

[Code]....