Need assistance with Macro

[oadmhernandez]

I came across the macro below and need your assistance in making it work only on the open workbook and active sheet.

Currently it opens the original workbook in which I originally copied the macro to.

Sub strip_zero_length_string()
Dim c As Long
With Sheets(ActiveSheet.Name).Cells(1, 1).CurrentRegion
.Cells = .Cells.Value
For c = 1 To .Columns.Count
.Columns(c).TextToColumns Destination:=.Cells(1, c), _
DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
Next c
End With
End Sub

 

[alansidman]

Put it in your personal.xlsb file. I suspect that it is resident in the original workbook and not the open workbook.

Look at this link for storing macros that need to be used universally in a personal macro book

How do I create and use a PERSONAL file for my VBA code

 

[oadmhernandez]

Alansidman, thank you very much. This worked like a charm.