How to specify macro for current active sheet

Solved/Closed
seo5906 - Updated on Nov 26, 2018 at 11:10 AM
 Dave - Mar 20, 2020 at 11:58 AM
Hello,
this is a very basic question, I am sorry to ask. I have created a macro in excel that I want to run in the current worksheet that is selected. How do I do this? For example, I have a sortZip Macro that I want to run on several worksheets when I hit the RUN button but it only works on the worksheet that I originally created the macro on.

I would really appreciate your advice on this!!! - Please :D

Code is:
Sub SortZip()
'
' SortZip Macro
'

'
    ActiveWorkbook.Worksheets("GilbertApril09").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("GilbertApril09").Sort.SortFields.Add Key:=Range( _
        "H2:H390"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("GilbertApril09").Sort.SortFields.Add Key:=Range( _
        "L2:L390"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("GilbertApril09").Sort
        .SetRange Range("A1:L390")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
System Configuration: Windows Vista Internet Explorer 7.0

6 replies

Very Easy!

Make a "find and replace" in your module, change every

Set shtJT = ActiveWorkbook.Sheets("xxxxx")


to the following:

Set shtJT = ActiveWorkbook.ActiveSheet


done.

Tony
114
thank you!! As usual, the simple solution is the best solution.
0
This is perfect. Thank you so much!!
0
Thanks alot
0
Many thanks! I knew it would be something simple :)
0
Thank you! Appreciate the question and answer!
0