Make a Macro work on the current sheet regardless of the sheet name

[ibcap]

Hoping this is a simple one (and not a "sorry... Excel doesn't do that" kind of thing). I would like to modify a Macro recorded / stored in my PERSONA.XLSB so that it will work on the current (active) sheet that I am on regardless of the sheet's name... e.g. maybe some way to wildcard it...

 

[6StringJazzer]

It would be helpful to see your code. Generically the way to do this is qualify references with the built-in object ActiveSheet. However, that's the default in a standard module (like Module1), so I don't know why you even need to change anything.

' Update a cell in Sheet1:
Worksheets("Sheet1").Range("A1") = "Test"

' Update a cell in the active sheet
ActiveSheet.Range("A1") = "Test"

' But this also updates a cell in the active sheet when the code is in a standard module (not a sheet module)
Range("A1") = "Test"

 

[ibcap]

Sorry it's taken me so long to get back to this...

First, thanks for your reply

A little more context for you... I've just been using the workaround of renaming the sheet when it doesn't already match what the Macro is looking for by default (matches about 90% of the time) and again... I am NOT a coder so I just recorded a Macro in Excel and saved it to my "PERSONAL.XLSB"

Here is the top portion of the current Macro as shown in the VBA Editor

' Keyboard Shortcut: Ctrl+K
'
Sub CameoKeyDecoder()
'
' CameoKeyDecoder Macro
' A VERY crude Cameo Key Parser... sheet must be renamed to "No Magic Floating License Serve" before running this Macro
'
' Keyboard Shortcut: Ctrl+K
'
Columns("A:A").Select
Columns("A:A").EntireColumn.AutoFit
ActiveWindow.SmallScroll Down:=0
Cells.Select
ActiveWorkbook.Worksheets("No Magic Floating License Serve").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("No Magic Floating License Serve").Sort.SortFields.Add2 Key:=Range( _
"A1:A949"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("No Magic Floating License Serve").Sort
.SetRange Range("A1:B949")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("A:A").Select
Selection.Replace What:="Vendor ", Replacement:="Vendor|", LookAt:=xlPart _

 

[6StringJazzer]

Just change

ActiveWorkbook.Worksheets("No Magic Floating License Serve")

to

ActiveWorksheet

 

[Alex Blakenburg]

It should be ActiveSheet

Your code is only sorting a hard coded range A1:B949 and so I am not sure how useful it is going to be as a generic macro.
In any case give this a try.
Note:
I have assumed you have headings in Row 1 and that your data starts in A1
I am using current region pivotting off A1, this means that if you have more columns immediately adjacent to column B they will be included in sort range.

Sub CameoKeyDecoder()
'
' CameoKeyDecoder Macro
' A VERY crude Cameo Key Parser... sheet must be renamed to "No Magic Floating License Serve" before running this Macro
'
' Keyboard Shortcut: Ctrl+K

'
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=Range("A1") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        
        .SetRange Range("A1").CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Columns("A").Replace What:="Vendor ", Replacement:="Vendor|", LookAt:=xlPart, _
        MatchCase:=False
End Sub

 

[6StringJazzer]

It should be ActiveSheet

Whoops. That's what I get for trying to do even the simplest thing out of my head without checking.