Re: help sorting multiple sheets

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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").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?


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
.

Relevant Pages

  • Re: help sorting multiple sheets
    ... Horatio J. Bilge, Jr. ... The error I am getting now is, "The sort reference is not valid. ... Private Sub cmdSortNumber_Click ... The unqualified ranges will refer to the activesheet if the code is in a general ...
    (microsoft.public.excel.misc)
  • Just a thank you from someone also helped by this
    ... It's these little bits of help here and there that help people like myself go from viewing Excel as taking up valuable disk space to being a really useful application that can save time and money in any number of ways. ... So I suppose I need to sort the entire sheet, not just the date column, ... I need to have my sheet setup to automatically sort data every time I ... I do not know what your ranges are so can't speak to that. ...
    (microsoft.public.excel)
  • Re: help with VBA
    ... I think that CLR's idea was not that you record a macro that does it for you ... structure and how to define the address ranges that the Sort command is going ... The first thing to do is to name the ranges you want sorted, ... then set the For..Next loop counter accordingly. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: help with VBA
    ... Within your loop you determine that you're at ... and know that you need to sort everything on all of the rows from 4 to this ... "indraneel" wrote: ... The first thing to do is to name the ranges you want sorted, ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Selecting multiple areas
    ... I didn't know you couldn't sort noncontiguous ranges. ... Jim T ... separate areas so they can be sorted together. ...
    (microsoft.public.excel.programming)