Steve Jackson wrote:
Private Sub CommandButton1_Click()
Sheets("Tables").Select
Range("B2:J6").Select
Selection.Sort Key1:=Range("J3"), Order1:=xlDescending,
Key2:=Range("I3") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal

Steve:

There are two problems. First, you are trying to select a range, B2:J6,
that's not on the active sheet. The difference between this code and the
code you copied from is location. When you're in a class module, like the
module where you code events for sheets, the default object is the sheet
whose class module you're in, not the active sheet. If you're in a standard
module, the active sheet is the default object. You need to prefix your
Ranges with the sheet reference. See also the "Default Object" section on
this page

http://www.dailydoseofexcel.com/arch...-object-model/

Second, you don't really need to select the range before you sort it. You
could, for instance, say

With Sheets("Table").Range("B2:J6")
.Sort Key1:= etc...
End With

For more information on selecting and activating, see

http://www.dailydoseofexcel.com/arch...-and-activate/

--
Dick Kusleika
MS MVP - Excel
www.dailydoseofexcel.com