S
Steve Jackson
I want to record a macro and then assign that macro to the onclick event of
a button. In the macro I have tried to open a different worksheet and sort
data in that worksheet. When I copy the text from the macro to the onclick
event of the button and click on the button I get the following error:
Runtime error 1004
Select Method of range class failed
It seems to failed after selecting the worksheet. Any help will be
appreciated.
Steve
What I have for the whole event is the following:
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
Range("B8:J12").Select
Selection.Sort Key1:=Range("J9"), Order1:=xlDescending,
Key2:=Range("I9") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=
_
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("B14:J18").Select
Selection.Sort Key1:=Range("J15"), Order1:=xlDescending, Key2:=Range( _
"I15"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
Range("B20:J24").Select
Selection.Sort Key1:=Range("J21"), Order1:=xlDescending, Key2:=Range( _
"I21"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
Range("L2:T6").Select
Selection.Sort Key1:=Range("T3"), Order1:=xlDescending,
Key2:=Range("S3") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=
_
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("L8:T12").Select
Selection.Sort Key1:=Range("T9"), Order1:=xlDescending,
Key2:=Range("S9") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=
_
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("L14:T18").Select
Selection.Sort Key1:=Range("T15"), Order1:=xlDescending, Key2:=Range( _
"S15"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
Range("L20:T24").Select
Selection.Sort Key1:=Range("T21"), Order1:=xlDescending, Key2:=Range( _
"S21"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
End Sub
a button. In the macro I have tried to open a different worksheet and sort
data in that worksheet. When I copy the text from the macro to the onclick
event of the button and click on the button I get the following error:
Runtime error 1004
Select Method of range class failed
It seems to failed after selecting the worksheet. Any help will be
appreciated.
Steve
What I have for the whole event is the following:
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
Range("B8:J12").Select
Selection.Sort Key1:=Range("J9"), Order1:=xlDescending,
Key2:=Range("I9") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=
_
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("B14:J18").Select
Selection.Sort Key1:=Range("J15"), Order1:=xlDescending, Key2:=Range( _
"I15"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
Range("B20:J24").Select
Selection.Sort Key1:=Range("J21"), Order1:=xlDescending, Key2:=Range( _
"I21"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
Range("L2:T6").Select
Selection.Sort Key1:=Range("T3"), Order1:=xlDescending,
Key2:=Range("S3") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=
_
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("L8:T12").Select
Selection.Sort Key1:=Range("T9"), Order1:=xlDescending,
Key2:=Range("S9") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=
_
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("L14:T18").Select
Selection.Sort Key1:=Range("T15"), Order1:=xlDescending, Key2:=Range( _
"S15"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
Range("L20:T24").Select
Selection.Sort Key1:=Range("T21"), Order1:=xlDescending, Key2:=Range( _
"S21"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
End Sub