I have a macro which produces a number of tables, one under each other
to show the sales statistics of a number of sales teams.

Because of the number of calculations involved the macro runs 1 team at
a time, copyies the values into a second worksheet, then calculates the
next team and copies the values under the first team and so on.

I need to add the ability to sort the tables (which an be of a varying
number of rows) by a number of different headings.

The obvious and simple way is to simply write a macro for each sort
option which sorts the tables before it copies the values to the second
worksheet.

However is there a way to add a variable as the sort key to determine
which field to sort by?

This is a section of the part of the macro. How do I determine the Key
range. This does not work:

Sheets("Team 1").Activate
Range(Cells(10, 1), Cells(formulas, 84)).Select

Dim sortrange

sortrange = Worksheets("Full Report").Cells(3, 1).Value

Selection.Sort Key1:=Range(Cells(10, sortrange)), Order1:=xlDescending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

Range(Cells(10, 1), Cells(formulas, 84)).Copy
Sheets("Full Report").Activate
Cells(lines - 1, 1).Select

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
_
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
_
SkipBlanks:=False, Transpose:=False




--
APealin