How to use excel vba to sort data on a hidden sheet

[sumgirl]

Hey all, I need to sort a hidden worksheet, and unhiding it to allow the sort is not really an option. I have some vba code that works great for a visible worksheet, but of course makes in its pants when ran against a hidden one...can someone help?:

----------------------------
Sheets(3).Select
With Selection
.Sort Key1:=.Cells(2, 1), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End With
----------------------------

Thanks!

 

[SkipVought]

Hi,

Do not use the Select Method

'[s]Sheets(3).Select [/s]
    With Sheets(3)
        .Sort Key1:=.Cells(2, 1), Order1:=xlAscending, _ 
            Header:=xlYes, OrderCustom:=1, MatchCase:=False, _ 
            Orientation:=xlTopToBottom 
    End With

I would also be careful about using numbers to index sheets. Changing the sheet order will cause an error. Check out the Sheet.CodeName property.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[sumgirl]

Hi Skip,
I got seriously excited for a second, but when I copy and paste that right into my vba, I get an error:

Run-time error '438':
Object doesnt support this property or method

With this higlighted:
.Sort Key1:=.Cells(2, 1), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

 

[SkipVought]

When you hide your sheet, IF the correct cell was not selected (a cell in the sort range), your original code would not have worked even if the sheet was not hidden.

Sorry I did not catch that.

    With Sheets(3)[b]
        .Cells(2, 1)[/b].Sort Key1:=.Cells(2, 1), Order1:=xlAscending, _ 
            Header:=xlYes, OrderCustom:=1, MatchCase:=False, _ 
            Orientation:=xlTopToBottom 
    End With

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[sumgirl]

Hang on, this worked:

With Sheets(3).Columns("A:H")
.Sort Key1:=.Cells(2, 1), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End With

Thanks for getting me on the track.