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

N

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.
 
T

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.
 
G

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
 
E

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.
 
J

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("B2:D4").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:D4")
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top