On Nov 9, 11:25*am, Gord Dibben wrote:
Cut all the macros and UDF's from the workbook(s) and paste them into
a module in a new workbook.

Save that workbook as an Add-in. *Keep the Add-in loaded so's code
will be available for all open workbooks.

OR......................

Save the code in your Personal Macro Workbook.

Gord

On Wed, 9 Nov 2011 06:25:22 -0800 (PST), Mr Molio
wrote:







On Nov 8, 3:13*pm, "Rick Rothstein"
wrote:
That works great!! Thanks!

Here is shorter UDF which will function the same as the one James posted...

Function IDKiller(ByVal S As String) As String
* * Dim X As Long
* * For X = 1 To Len(S)
* * * * If Mid(S, X, 10) Like "##########" Then
* * * * * * Mid(S, X) = "XXXXXXXXXX"
* * * * * * Exit For
* * * * End If
* * Next
* * IDKiller = S
End Function

Rick Rothstein (MVP - Excel)

Thanks, Rick!

Now, a new wrinkle (most likely in my brain!) - When I was originally
testing this, I had a dummy workbook open in which I added a module,
pasted in the function code, etc. I wrote a loop function to run down
the column, use this function to mask the account numbers, and then
paste the result (as a value) over the original cell. All worked
great.

I would like this function and macro to be available for new sheets,
of the same layout, since we generate these sheets every week. We took
the original file (with the module) and did as Save As to create a new
week's file. Closed Excel. I reopened Excel, opened the NEW file, and
ran the macro. Worked fine, except that, in the background, it also
opened the original file as well.

For the life of me I can't find anything in the code that references
the original file, or any file structure location. Is there somewhere
OTHER than the module code that could be telling it that the code was
originally written in ANOTHER file, and needs to open that file?

Here is the code in my module (MY bits are very un-elegant, so I'm
open to suggestions on that as well, but right now I'm just trying to
figure out what's opening that original file)

=========BEGIN CODE=============

Function IDKiller(s As String) As String
Dim L As Long, i As Long
Dim J As Long, JJ As Long
L = Len(s)
ReDim ary(1 To L)
For i = 1 To L
* *ary(i) = Mid(s, i, 1)
Next
kount = 0
J = 0
JJ = 0
For i = 1 To L
* *If ary(i) Like "#" Then
* * * *kount = kount + 1
* * * *If J = 0 Then J = i
* * * *JJ = i
* * * *If kount = 12 Then Exit For
* *Else
* * * *kount = 0
* * * *J = 0
* * * *JJ = 0
* *End If
Next
If kount = 12 Then
* *For jjj = J To JJ
* * * *ary(jjj) = "x"
* *Next
End If
IDKiller = ""
For i = 1 To L
* *IDKiller = IDKiller & ary(i)
Next
End Function

Sub InsertIDKILLER()
* 'code to insert the function, then copy the results and paste as
VALUE over the original cell

* *ActiveCell.FormulaR1C1 = "=idkiller(RC[-5])" * *'the function is
going in a blank cell 5 columns over from the content cell
* *ActiveCell.Offset(-1, 0).Select * * * * * * * * * * * * *'when it
runs, it drops down to the next row, so I'm taking it back up to copy
the results
* *Selection.Copy
* *Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
* * * *:=False, Transpose:=False
* *Selection.WrapText = True

* *ActiveCell.Offset(1, 0).Range("A1").Select * * * *'this section
takes the value-copied results and replaces the original content cell
with them, then deletes the "working" cell
* *Selection.Copy
* *ActiveCell.Offset(0, -5).Range("A1").Select
* *Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
* * * *:=False, Transpose:=False
* *ActiveCell.Offset(0, 5).Range("A1").Select
* *Application.CutCopyMode = False
* *Selection.ClearContents

End Sub

Sub RunThatThing()
*'loop code

* *Do Until IsEmpty(ActiveCell.Offset(0, -5))
* * * *InsertIDKILLER
* * * *ActiveCell.Offset(1, 0).Select
* *Loop
End Sub

=====END CODE=====

Thanks for everyone's help with this!

Gord,

Thanks, I'll try the Add In route. I tried the PMW but it didn't seem
to change the behaviour. Still very likely USER error, but I'm
learning by tracking it down!