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

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

I tried the code you suggested, and I got the same problem. I'm not exactly
clear what you mean by qualified and unqualified ranges. You are correct that
the code is in a worksheet module. The command buttons are located on sheet1,
so the code ended up on the sheet1 module.

Should I put the code in a general module, and then use the command buttons
to call the code? If so, what would that look like?

Thanks,
~ Horatio

"Dave Peterson" wrote:

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

A qualified range:
workbooks("book1.xls").worksheets("sheet999").rang e("a1:b99")

An unqualified range:
range("a1:b99")

The qualified range is explicit. You tell it exactly what you want.

An unqualified range depends on the rules of excel's VBA.

The code worked fine for me.

Any chance you have protected worksheets or merged cells within those ranges to
be sorted?

Can you successfully sort the ranges manually?


Horatio J. Bilge, Jr. wrote:

I tried the code you suggested, and I got the same problem. I'm not exactly
clear what you mean by qualified and unqualified ranges. You are correct that
the code is in a worksheet module. The command buttons are located on sheet1,
so the code ended up on the sheet1 module.

Should I put the code in a general module, and then use the command buttons
to call the code? If so, what would that look like?

Thanks,
~ Horatio

"Dave Peterson" wrote:

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


--

Dave Peterson

Thanks for the explanation.
The error I am getting now is, "The sort reference is not valid. Make sure
that it's within the data you want to sort, and the first Sort By box isn't
the same or blank." When I click on Debug, it is the ".sort" section for
sheet2 that is highlighted.

There aren't any protected worksheets or merged cells, and I am able to sort
the ranges manually successfully.

I am using named ranges, (Sheet1!A2:C50 is named "Data_1", and Sheet2!A2:C50
is named Data_2), but I tried it without using the named ranges, and got the
same error message.

~ Horatio


"Dave Peterson" wrote:

A qualified range:
workbooks("book1.xls").worksheets("sheet999").rang e("a1:b99")

An unqualified range:
range("a1:b99")

The qualified range is explicit. You tell it exactly what you want.

An unqualified range depends on the rules of excel's VBA.

The code worked fine for me.

Any chance you have protected worksheets or merged cells within those ranges to
be sorted?

Can you successfully sort the ranges manually?


Horatio J. Bilge, Jr. wrote:

I tried the code you suggested, and I got the same problem. I'm not exactly
clear what you mean by qualified and unqualified ranges. You are correct that
the code is in a worksheet module. The command buttons are located on sheet1,
so the code ended up on the sheet1 module.

Should I put the code in a general module, and then use the command buttons
to call the code? If so, what would that look like?

Thanks,
~ Horatio

"Dave Peterson" wrote:

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


--

Dave Peterson

Did you change the code?

Try looking for those leading dots--like in front of .columns().



Horatio J. Bilge, Jr. wrote:

Thanks for the explanation.
The error I am getting now is, "The sort reference is not valid. Make sure
that it's within the data you want to sort, and the first Sort By box isn't
the same or blank." When I click on Debug, it is the ".sort" section for
sheet2 that is highlighted.

There aren't any protected worksheets or merged cells, and I am able to sort
the ranges manually successfully.

I am using named ranges, (Sheet1!A2:C50 is named "Data_1", and Sheet2!A2:C50
is named Data_2), but I tried it without using the named ranges, and got the
same error message.

~ Horatio

"Dave Peterson" wrote:

A qualified range:
workbooks("book1.xls").worksheets("sheet999").rang e("a1:b99")

An unqualified range:
range("a1:b99")

The qualified range is explicit. You tell it exactly what you want.

An unqualified range depends on the rules of excel's VBA.

The code worked fine for me.

Any chance you have protected worksheets or merged cells within those ranges to
be sorted?

Can you successfully sort the ranges manually?


Horatio J. Bilge, Jr. wrote:

I tried the code you suggested, and I got the same problem. I'm not exactly
clear what you mean by qualified and unqualified ranges. You are correct that
the code is in a worksheet module. The command buttons are located on sheet1,
so the code ended up on the sheet1 module.

Should I put the code in a general module, and then use the command buttons
to call the code? If so, what would that look like?

Thanks,
~ Horatio

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson

That's it! I was just missing the dots in front of the columns.
Funny that it worked without the dots on sheet1, but not on sheet2.

Thanks,
~ Horatio


"Dave Peterson" wrote:

Did you change the code?

Try looking for those leading dots--like in front of .columns().



Horatio J. Bilge, Jr. wrote:

Thanks for the explanation.
The error I am getting now is, "The sort reference is not valid. Make sure
that it's within the data you want to sort, and the first Sort By box isn't
the same or blank." When I click on Debug, it is the ".sort" section for
sheet2 that is highlighted.

There aren't any protected worksheets or merged cells, and I am able to sort
the ranges manually successfully.

I am using named ranges, (Sheet1!A2:C50 is named "Data_1", and Sheet2!A2:C50
is named Data_2), but I tried it without using the named ranges, and got the
same error message.

~ Horatio

"Dave Peterson" wrote:

A qualified range:
workbooks("book1.xls").worksheets("sheet999").rang e("a1:b99")

An unqualified range:
range("a1:b99")

The qualified range is explicit. You tell it exactly what you want.

An unqualified range depends on the rules of excel's VBA.

The code worked fine for me.

Any chance you have protected worksheets or merged cells within those ranges to
be sorted?

Can you successfully sort the ranges manually?


Horatio J. Bilge, Jr. wrote:

I tried the code you suggested, and I got the same problem. I'm not exactly
clear what you mean by qualified and unqualified ranges. You are correct that
the code is in a worksheet module. The command buttons are located on sheet1,
so the code ended up on the sheet1 module.

Should I put the code in a general module, and then use the command buttons
to call the code? If so, what would that look like?

Thanks,
~ Horatio

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson

That means that sheet1 was the owner of the code. And that the unqualified
range belongs to that sheet.

Horatio J. Bilge, Jr. wrote:

That's it! I was just missing the dots in front of the columns.
Funny that it worked without the dots on sheet1, but not on sheet2.

Thanks,
~ Horatio

"Dave Peterson" wrote:

Did you change the code?

Try looking for those leading dots--like in front of .columns().



Horatio J. Bilge, Jr. wrote:

Thanks for the explanation.
The error I am getting now is, "The sort reference is not valid. Make sure
that it's within the data you want to sort, and the first Sort By box isn't
the same or blank." When I click on Debug, it is the ".sort" section for
sheet2 that is highlighted.

There aren't any protected worksheets or merged cells, and I am able to sort
the ranges manually successfully.

I am using named ranges, (Sheet1!A2:C50 is named "Data_1", and Sheet2!A2:C50
is named Data_2), but I tried it without using the named ranges, and got the
same error message.

~ Horatio

"Dave Peterson" wrote:

A qualified range:
workbooks("book1.xls").worksheets("sheet999").rang e("a1:b99")

An unqualified range:
range("a1:b99")

The qualified range is explicit. You tell it exactly what you want.

An unqualified range depends on the rules of excel's VBA.

The code worked fine for me.

Any chance you have protected worksheets or merged cells within those ranges to
be sorted?

Can you successfully sort the ranges manually?


Horatio J. Bilge, Jr. wrote:

I tried the code you suggested, and I got the same problem. I'm not exactly
clear what you mean by qualified and unqualified ranges. You are correct that
the code is in a worksheet module. The command buttons are located on sheet1,
so the code ended up on the sheet1 module.

Should I put the code in a general module, and then use the command buttons
to call the code? If so, what would that look like?

Thanks,
~ Horatio

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson