help sorting multiple sheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Horatio J. Bilge, Jr. - 21 May 2008 14:57 GMT

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

Reply to this Message

Dave Peterson - 21 May 2008 15:18 GMT

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.

> 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
[quoted text clipped - 37 lines]
> Header:=xlNo
> End Sub

Signature

Dave Peterson

Reply to this Message

Horatio J. Bilge, Jr. - 21 May 2008 15:39 GMT

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

> Don't select the sheets and qualify all your ranges:
>
[quoted text clipped - 85 lines]
> > Header:=xlNo
> > End Sub

Reply to this Message

Dave Peterson - 21 May 2008 16:51 GMT

A qualified range:
workbooks("book1.xls").worksheets("sheet999").range("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?

> 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
[quoted text clipped - 100 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Horatio J. Bilge, Jr. - 21 May 2008 17:34 GMT

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

> A qualified range:
> workbooks("book1.xls").worksheets("sheet999").range("a1:b99")
[quoted text clipped - 117 lines]
> > >
> > > Dave Peterson

Reply to this Message

Dave Peterson - 21 May 2008 19:16 GMT

Did you change the code?

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

> Thanks for the explanation.
> The error I am getting now is, "The sort reference is not valid. Make sure
[quoted text clipped - 136 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Horatio J. Bilge, Jr. - 21 May 2008 19:52 GMT

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

> Did you change the code?
>
[quoted text clipped - 140 lines]
> > >
> > > Dave Peterson

Reply to this Message

Dave Peterson - 21 May 2008 20:31 GMT

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

> 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.
[quoted text clipped - 150 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message