When you record a macro that does something like sorting, typically it
annotates the specific sheet that was active when you recorded it. Also it
will do something like selecting the cells you want to take action on. These
are both things you probably don't want in your 'generic' macro. So you
might end up with code that looks something like this:
Sub Macro1()
Sheets("Sheet1").Select
Range("B2
4").Select
Selection.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select
End Sub
The first thing to do away with would be the Sheets("Sheet1").Select
statement. Then (here's the answer to your question), set up a couple of
range variables to use as part of the sort
Dim sRange as Range
Dim sKey as Range
and set them to the current ActiveSheet!!And modify the code accordingly:
Set sRange=ActiveSheet.Range("B2
4")
Set sKey = ActiveSheet.Range("B2")
sRange.Sort Key1:=sKey, Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
And you're done with it.