Don't select the sheets and qualify all your ranges:

Option Explicit
Private Sub cmdSortAlpha_Click()
with Worksheets("Sheet1").Range("A2:C50")
.Sort _
Key1:=.columns(1), _
Order1:=xlAscending, _
Key2:=.columns(2), _
Order2:=xlAscending, _
Header:=xlNo
end with

with Worksheets("Sheet2").Range("A2:C50")
.Sort _
Key1:=.columns(1), _
Order1:=xlAscending, _
Key2:=.columns(2), _
Order2:=xlAscending, _
Header:=xlNo
end with
End Sub

Private Sub cmdSortNumber_Click()

with Worksheets("Sheet1").Range("A2:C50")
.Sort _
Key1:=.columns(3), _
Order1:=xlAscending, _
Header:=xlNo
end with

with Worksheets("Sheet2").Range("A2:C50")
.Sort _
Key1:=.columns(3), _
Order1:=xlAscending, _
Header:=xlNo
end with

End Sub

The unqualified ranges will refer to the activesheet if the code is in a general
module. But those unqualified ranges will refer to the sheet that owns the code
if the code is under a worksheet module.

And from the names of the procedures, it looks like the code is under a
worksheet module.

Horatio J. Bilge, Jr. wrote:

I am trying to create vba code to sort multiple sheets by clicking on a
command button. I got it to sort the first page correctly, (the page the
button is on), but I'm having trouble getting it to sort other pages. I get
this error: "Method 'Range' of object '_Worksheet' failed."

On each sheet, I have three columns with headers: first name (columnA), last
name(columnB), and number (columnC).

Here is the code I have so far. I have one sub to sort the sheets
alphabetically, and one to sort by number:

Option Explicit
Private Sub cmdSortAlpha_Click()
Worksheets("Sheet1").Select
Worksheets("Sheet1").Range("A2:C50").Sort _
Key1:=Range("A2:A50"), _
Order1:=xlAscending, _
Key2:=Range("B2:B50"), _
Order2:=xlAscending, _
Header:=xlNo
Worksheets("Sheet2").Select
Worksheets("Sheet2").Range("A2:C50").Sort _
Key1:=Range("A2:A50"), _
Order1:=xlAscending, _
Key2:=Range("B2:B50"), _
Order2:=xlAscending, _
Header:=xlNo
End Sub

Private Sub cmdSortNumber_Click()
Worksheets("Sheet1").Select
Worksheets("Sheet1").Range("A2:C50").Sort _
Key1:=Range("C2:C50"), _
Order1:=xlAscending, _
Header:=xlNo
Worksheets("Sheet2").Select
Worksheets("Sheet2").Range("A2:C50").Sort _
Key1:=Range("C2:C50"), _
Order1:=xlAscending, _
Header:=xlNo
End Sub

--

Dave Peterson