Select all Worksheets in Workbook without knowing how many or WS Names 2

[sterlecki]

How would I select all the worksheets in a workbook without knowing how many worksheets a workbook contained and without using the worksheet names specifically.

The macro recorder gives me this:

Sheets(Array("Mesa 8D2-21 S4", "Mesa 8D2-21 S1", "Mesa 8D2-21 S2","Mesa 8D2-21 S3", "Sheet1")).Select

But I need to use something more generic.

I can get the number of worksheets from:
shtCount = ActiveWorkbook.Worksheets.Count

I know they have index numbers but I don't know the proper syntax.

How would I reference them in an array such as from
1 to shtcount

I don't need to loop through them just select them all without knowing the names.

 

[mintjulep]

Something along the lines of:

Dim All_Sheets() as Variant
Redim All_Sheets(Sheets.count)

For Count = 0 to Sheets.count-1
All_Sheets(Count)= Count + 1
Next Count

Sheets(All_Sheets).select

But why do you need to select the sheets? If you want to do something to them, just do it. Most likely there is no need to select.

 

[macropod]

Hi sterlecki,

You could select all with:
ThisWorkbook.Sheets.Select
but that's not going to solve your problem, since that won't give you something you can work with.

Try something along the lines of:

Sub LoopThruAllSheets()
Dim xlSht As Worksheet
' loop through all sheets in WB
For Each xlSht In ActiveWorkbook.Worksheets
  With xlSht
  ' <your code here>
  End With
 Next xlSht
End Sub

Cheers
Paul Edstein
[MS MVP - Word]

 

[sterlecki]

Thanks mintjulep and macropod. The simplest answer is usually the best. By using the macro recorder and right clicking select all tabs I thought I needed and array.

As I said I didn't need to do a loop as I had already done my calculations and page setups for printing.

I just needed to select all the tabs with info on them for a print preview.

Answer that worked was:

ActiveWorkbook.Sheets.Select
(so simple this caveman couldn't do it!)

Thanks for your other suggestions though I will probably use them in the future.

 

[SkipVought]

is there a reason that you need to SELECT all sheets?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mintjulep]

No need to SELECT, just PrintPreview

ActiveWorkbook.Sheets.PrintPreview

 

[macropod]

Hi Skip,

Selecting all is useful for applying the same page layout to all sheets in one go. I'm not sure if it has many other uses, though.

Cheers
Paul Edstein
[MS MVP - Word]