Run-Time error '1004' : Select method of Range class failed

  • Thread starter Thread starter matthew.a.lawless
  • Start date Start date
M

matthew.a.lawless

I have some code that works from a keyboard shortcut, but when I try
to attach it to a command button on another worksheet is delivers the
above error. Code is below, please help!

Sheets("Table").Activate
Range("E5:M17").Select
Selection.Sort Key1:=Range("M6"), Order1:=xlDescending,
Key2:=Range("L6") _
, Order2:=xlDescending, Key3:=Range("J6"),
Order3:=xlDescending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal
Range("A1").Select
 
When the code is in a general module (not under a worksheet), then unqualified
ranges belong to the active sheet.

When the code is behind a worksheet, then unqualified ranges belong to the sheet
that owns the code.

So when you write:
range("e5:M17").select
it's refering to the sheet with the commandbutton--not Table

You could qualify each range:
worksheets("table).range("e5:m17").select
(kind of a pain)

Or you could drop the select's:

with worksheets("Table")
with .range("E5:M17")
.cells.sort Key1:=.columns(9), Order1:=xlDescending, _
Key2:=.columns(8), Order2:=xlDescending, _
Key3:=.columns(6), Order3:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, DataOption3:=xlSortNormal
end with
end with

And you know if your data has headers, right. I'd change xlguess to xlyes or
xlno. I don't like excel to guess at anything I could specify.
 
One more followup...

So when you write:
range("e5:M17").select
it's refering to the sheet with the commandbutton--not Table

And since you're trying to select a range on a non-active sheet (the sheet with
the commandbutton), you get the error.
 
Thanks, works great now.

One more followup...

So when you write:
range("e5:M17").select
it's refering to the sheet with the commandbutton--not Table

And since you're trying to select a range on a non-active sheet (the sheet with
the commandbutton), you get the error.








--

Dave Peterson- Hide quoted text -

- Show quoted text -
Click to expand...
 
Back
Top