Sort macro in hidden sheets

[Nick]

hello

can anyone tell me if it is possible to run a sort macro
across hidden sheets. the spreadsheet i have at the
moment finds an error when i don't activate the hidden
sheets. at the moment i am putting

Sheets("Sheet_Name").Visible = True (before the sort
process) AND

Sheets("Sheet_Name").Select
ActiveWindow.SelectedSheets.Visible = False (after the
sort process)

when you run this macro it looks very ugly to skip across
and hide and unhide all the sheets. can this be fixed?

using
Application.ScreenUpdating = False

does not stop it skipping and showing the process of
hiding and unhiding all the sheets for the sort

thanks
Nick

 

[Bob Phillips]

Nick,

I didn't do a sort, but I did a simple test on showing a sheet, doing some
activity and hiding it again, and I didn't get it showing on screen. I got
an hourglass when it was busy, but I never saw the sheet1. Is that all of
your code?

Dim oWS As Worksheet, i
Application.ScreenUpdating = False
Set oWS = ActiveSheet
With Worksheets("Sheet1")
.Visible = True
.Activate
For i = 1 To 10000
Cells(i, 1) = i
Cells(i, 2) = i + 1
Next
.Visible = False
End With
oWS.Activate
Application.ScreenUpdating = True

 

[Dave Peterson]

I bet that it wasn't the visibility of the worksheet that caused the problem.

My bet is you had an unqualified range. And an unqualified range means that
range refers to the activesheet (when the code is in a general module).

By selecting the sheet, you made that unqualified range refer to the correct
sheet.

So if your code looked like this:

worksheets("sheet_name").Range("B8:I23").Sort Key1:=Range("B8"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

That range("b8") refered to what ever sheet was active. If the correct sheet is
active, success. If it's not active, kablewie!

You can fix this kind of error by making sure you say what sheet owns that B8.

worksheets("sheet_name").Range("B8:I23").Sort _
Key1:=worksheets("sheet_name").Range("B8"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

But that's way to much typing:

with worksheets("sheet_name")
.Range("B8:I23").Sort _
Key1:=.Range("B8"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
end with

Note those dots in front of both Ranges. That says that they each belong to the
previous With (which was the worksheet that want.)