How can I enable a macro to run in a hidden worksheet?

[curtc]

It presently runs only when unhidden. Conversely, If I could protect the
worksheet from user changes (but still allow the macro to operate in this
individual worksheet) it would accomplish the same goal. The macro sorts data
independently in each column. The name of the sheet is "sort".
Thank You.

 

[ryguy7272]

Unhide the sheet! This may give you some ideas of how ot get started:
http://excel.tips.net/Pages/T002548_Running_Macros_on_Hidden_Worksheets.html

http://www.teachexcel.com/macros/delete_hidden_worksheets.php

http://stackoverflow.com/questions/...re-possible-on-hidden-worksheets-or-workbooks


HTH,
Ryan--

 

[Dave Peterson]

My bet is that you're selecting ranges on that hidden sheet. And you can only
select a range if that sheet is active. And those hidden sheets will never be
active.

So you could save where the user is, unhide the sheet, select the sheet, select
the range to sort, hide the sheet, and go back to where the user started.

And if you hide your actions with:
application.screenupdating = false
'lots of code here
application.screenupdating = true

But better is to drop the .select's from your code.

Dim RngToSort as range

with worksheets("sort")
'this sorts Column A:X from row 1 to the last row used in column A
'you didn't share what you were sorting!
set rngtosort = .range("A1:X" & .cells(.rows.count,"A").end(xlup).row)
end with

with rngtosort
.Cells.Sort key1:=.Columns(1), Order1:=xlDescending, _
Key2:=.Columns(3), Order2:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
End With
'this sorted by column 1 (descending) and then column 3 (ascending).
'you didn't share how you were sorting!