Select all sheets in VBA

[oliver_penney]

there is probably a one line answer to this, but how do i select all (visible) sheets?

if a record a macro it creates an array of the worksheet names, which is no good if the names change

thanx in advance

ol

 

[Mark O'Brien]

Welcome to the Board!<pre>
Sheets.Select</pre>

that should do it.

EDIT:: Should've tested that with some hidden sheets really shouldn't I? Nate Oliver's solution is the way to go.
_________________<font color = green> Mark O'Brien </font>

Columbus Ohio Celtic Supporters Club
This message was edited by Mark O'Brien on 2002-12-19 10:48

 

[TommyGun]

ActiveWorkbook.Sheets.Select

 

[NateO]

Howdy Oliver (sharp name!), welcome to the board. How's about the following:

<pre>
Sub gram_em()
Dim ws As Worksheet
For Each ws In Sheets
If ws.Visible Then ws.Select (False)
Next
End Sub</pre>

 

[oliver_penney]

ta Nate it was that ****ing false thing i'd missed

 

[Badai]

Hi Nate,

What the use for "(false)" in that syntax?
If i using that syntax, error message appear

"run time error '1004'
application-defined or object-defined error"

But when i delete the (false), all the syntax work like charm

 

[James Snyder]

It was 2002 when Nate wrote that. I don't think it likely that he is still subscribed to this thread.

 

[PaulGillespie]

Hi Nate,

What the use for "(false)" in that syntax?
If i using that syntax, error message appear

"run time error '1004'
application-defined or object-defined error"

But when i delete the (false), all the syntax work like charm

I believe the (false) flag tells Excel not to replace whatever's currently selected with the object you're selecting (I think it only works with worksheets; not sure it works with ranges, etc.). (true) would tell it to select the sheet as the only sheet selected; (false) tells it to keep selecting.

 

[robertpas]

I know it is an old thread, but it was usefully to me....
Remove the brackets () around FALSE

 

[Anne Troy]

Howdy Oliver (sharp name!), welcome to the board. How's about the following:

<pre>
Sub gram_em()
Dim ws As Worksheet
For Each ws In Sheets
If ws.Visible Then ws.Select (False)
Next
End Sub</pre>

I miss you, Nate.