Select all worksheets

B

BuddieB

Board Regular

Joined

Aug 6, 2003

Messages

174

• Dec 3, 2003

• #2

I use the following code:

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

 

Upvote 0

parry

MrExcel MVP

Joined

Aug 20, 2002

Messages

3,355

• Dec 3, 2003

• #3

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.

Code:

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

 

Upvote 0

NateO

Legend

Joined

Feb 17, 2002

Messages

9,700

• Dec 3, 2003

• #4

parry said:

Sheets property also accepts numbers as well as Sheet names

Click to expand...

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>

 

Upvote 0

M

Mudface

MrExcel MVP

Joined

Feb 18, 2002

Messages

3,339

• Dec 3, 2003

• #5

Code:

Public Sub SelectAll()

Sheets.Select ' Or Worksheets.Select

End Sub

 

Upvote 0

parry

MrExcel MVP

Joined

Aug 20, 2002

Messages

3,355

• Dec 3, 2003

• #6

NateO said:

parry said:

Sheets property also accepts numbers as well as Sheet names

Click to expand...

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>

Click to expand...

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.

 

Upvote 0

NateO

Legend

Joined

Feb 17, 2002

Messages

9,700

• Dec 3, 2003

• #7

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!

 

Upvote 0

parry

MrExcel MVP

Joined

Aug 20, 2002

Messages

3,355

• Dec 3, 2003

• #8

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.

 

Upvote 0

NateO

Legend

Joined

Feb 17, 2002

Messages

9,700

• Dec 3, 2003

• #9

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.

 

Upvote 0

tusharm

MrExcel MVP

Joined

May 28, 2002

Messages

11,029

• Dec 3, 2003

• #10

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

Code:

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?

Jonbob said:

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

Thanks,
Jonbob

Click to expand...

 

Upvote 0