Select all worksheets

[Jonbob]

I have 200 worksheets in a workbook. What macro is there to select all worksheets?

Thanks,
Jonbob

 

[BuddieB]

I use the following code:

    Dim Arr() As String
    Dim i As Integer
    ReDim Arr(Sheets.Count - 1)
    For i = 0 To Sheets.Count - 1
        Arr(i) = Sheets(i + 1).Name
    Next i
    Sheets(Arr).Select

I can't take credit for it, but I don't remember who to credit for coming up with it when I was in need of this same feature. Sorry

 

[parry]

Hi BuddieB, you can also do this as the Sheets property also accepts numbers as well as Sheet names. Note that MyArray is declared as an integer rather than a string in this case.

Sub SelectAllSheets()
Dim MyArray() As Integer, i As Integer
ReDim MyArray(1 To Sheets.Count)

For i = 1 To Sheets.Count
MyArray(i) = i
Next i

Sheets(MyArray).Select

End Sub

 

[NateO]

Sheets property also accepts numbers as well as Sheet names

And with this mind,

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> test()
Sheets(Evaluate("transpose(row(1:" & Sheets.Count & _
    "))")).<SPAN style="color:darkblue">Select</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

 

[Mudface]

Public Sub SelectAll()

Sheets.Select ' Or Worksheets.Select

End Sub

 

[parry]

Sheets property also accepts numbers as well as Sheet names

And with this mind,

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> test()
Sheets(Evaluate("transpose(row(1:" & Sheets.Count & _
    "))")).<SPAN style="color:darkblue">Select</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

You goys always have to go one better. btw, I think Mudface wins.

Nate, I cant understand this at all even though I have looked Evaluate up under help.

 

[NateO]

The help file doesn't do Evaluate much justice, see Monsieur Longre's sapient advice here:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=366730CB.1FB1@wanadoo.fr&rnum=5

The transpose function is implemented to convert the 2-d array to a 1-d, which Excel wants here.

And I concur, Mudface wins!

 

[parry]

Thanks Nate, Evaluate is something I would have to use a few times for it to sink in properly but I think I understand what you have done now.

 

[NateO]

You are welcome.

This is one of several potential uses of the Evaluate method mind you.

Enter the following in a worksheet:

=TRANSPOSE(ROW(1:10))

Hit your function wizard to review the return. You're simply cranking out, and using, this array in VBA.

 

[tusharm]

Why not use the GUI? Click on the tab of the first worksheet. Click on the VCR button that moves all the way to the right. Hold down SHIFT and click on the tab of the last worksheet.

And, if you must have a VBA solution, use

Sub testIt()
    ActiveWorkbook.Sheets.Select
    End Sub

Simple, eh? [Edit: I just noticed Mudface has already mentioned it. Oops! ]

That said...200 worksheets? For the life of me, I cannot envison any business / scientific / education scenario that would require that many worksheets in a single workbook. Mind sharing what you are doing?

I have 200 worksheets in a workbook. What macro is there to select all worksheets?

Thanks,
Jonbob