How do I get a macro to be non worksheet specific?

[navel151]

I can't figure out how to make a macro non worksheet specific like they were
in previous versions of Excel. I want to have a macro that will sort the
same range of cells of whatever sheet that I am in, rather than having the
same macro for each sheet with a different name.

 

[trip_to_tokyo]

In EXCEL 2007:-

When you record your Macro for the first time you will get the Macro panel
up (where you name it).

In here there is a section called:-

Store macro in:

It is here that you can select one of the options to make the macro
available outside of that Workbook only.

Please hit yes if my comments have helped.

Thanks.

 

[Gord Dibben]

No different than previous versions of Excel.

Use ActiveSheet in your code.

e.g. ActiveSheet.Range("A1:A10").Interior.ColorIndex = 3

more specific to your needs............

ActiveSheet.Columns("C:F").Select
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=2, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


Gord Dibben MS Excel MVP

 

[Eva]

This in an example. Change sort key1 range to whatever you need

Sub Macro2()
Selection.CurrentRegion.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

Click yes if it helped.

 

[JLatham]

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("B24").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("B24")
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.

 

[ryguy7272]

Some good resources:
http://www.anthony-vba.kefra.com/vba/vbabasic1.htm
http://www.rondebruin.nl/code.htm
http://www.contextures.com/xlvba01.html

 

[Gord Dibben]

How will that make the macro non-sheet specific?


Gord