Question about For Each ws in Worksheets

[Orion Cochrane]

I have a reporting program I wrote on Excel. Basically, what it does is
generate reports for accounting purposes based on activity. I have a sheet I
want hidden, as no one needs it but me for data validation. Here is a snippet
of my macro:
Private Sub Test()
For Each ws in Worksheets
ws.Activate
[actions]
End Sub
My question is in the ws.Activate line: Using this line, will it activate my
hidden sheet? If so, I can work around that. Thanks.

 

[Orion Cochrane]

Never mind. It does activate hidden sheets. I added to my workaround and my
program works fine now.

 

[Don Guillett]

It may be interresting to see what you did because it is NOT necessary to
activate a sheet to do most things.

For Each ws in Worksheets ws.[actions]
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Never mind. It does activate hidden sheets. I added to my workaround and
my
program works fine now.
--
I am running on Excel 2003, unless otherwise stated. Please rate posts so
we
know when we have answered your questions. Thanks.

I have a reporting program I wrote on Excel. Basically, what it does is
generate reports for accounting purposes based on activity. I have a
sheet I
want hidden, as no one needs it but me for data validation. Here is a
snippet
of my macro:
Private Sub Test()
For Each ws in Worksheets
ws.Activate
[actions]
End Sub
My question is in the ws.Activate line: Using this line, will it activate
my
hidden sheet? If so, I can work around that. Thanks.

 

[RyanH]

You could try this. I do agree with Don, I wouldn't think it is neccessary
to activate each sheet to do the actions. It may make your code more
efficient.

' this will exclude the named worksheet
Private Sub Test()
For Each ws in Worksheets
If Not ws.Name Is "Hidden Sheet Name" Then
ws.Activate
[actions]
End If
Next ws
End Sub

or

' this will exclude all hidden worksheets
Private Sub Test()
For Each ws in Worksheets
If Not ws.Visible = False Then
ws.Activate
[actions]
End If
Next ws
End Sub

 

[Orion Cochrane]

I like that second example excluding the hidden sheets. There was only one
hidden sheet in my actual program, and the sheet name was short, so I
excluded that sheet. Thanks, though. I am sure I will need that example
elsewhere.
--
I am running on Excel 2003, unless otherwise stated. Please rate posts so we
know when we have answered your questions. Thanks.

You could try this. I do agree with Don, I wouldn't think it is neccessary
to activate each sheet to do the actions. It may make your code more
efficient.

' this will exclude the named worksheet
Private Sub Test()
For Each ws in Worksheets
If Not ws.Name Is "Hidden Sheet Name" Then
ws.Activate
[actions]
End If
Next ws
End Sub

or

' this will exclude all hidden worksheets
Private Sub Test()
For Each ws in Worksheets
If Not ws.Visible = False Then
ws.Activate
[actions]
End If
Next ws
End Sub

--
Cheers,
Ryan

I have a reporting program I wrote on Excel. Basically, what it does is
generate reports for accounting purposes based on activity. I have a sheet I
want hidden, as no one needs it but me for data validation. Here is a snippet
of my macro:
Private Sub Test()
For Each ws in Worksheets
ws.Activate
[actions]
End Sub
My question is in the ws.Activate line: Using this line, will it activate my
hidden sheet? If so, I can work around that. Thanks.