Using a macro in other workbooks

[mack321]

So, I have a macro that works beautifully when saved in the workbook it is being used in but when I try to move it to my Personal macro workbook or even to a resident workbook in my C drive and call it from a new workbook, it will not run. It doesn't error out, it just does nothing. What am I missing?
I have others that work across any/all workbooks beautifully... just this one.

Sub Insert_Formula()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Activate
Range("$J$2").Formula = "=MID($C$6,FIND(""Customer Ord No:"",$C$6)+17,FIND("", R"",$C$6)-FIND(""Customer Ord No:"",$C$6)-17)"
Next
End Sub

Also, note that each workbook I am running this in is identical in layout and format.

 

[JoeMo]

Change:
ThisWorkbook.Worksheets

to:

ActiveWorkbook.Worksheets

Then put the macro in your Personal.xlsb workbook and remove it from all other workbooks. Now you should be able to run it on any workbook that is active by adding a button to your Quick-Access Toolbar to trigger it.

 

[mack321]

Perfect! Thank you. Should have known...