ADD sheets to array

[eighty2scrambler]

What VBA code would I use to add all sheets in a workbook to an array EXCEPT "Sheet1" ??

The sheet names will change but "sheet1" will always stay the same.

After getting them added to the array , I want to sum Column E in Cell F1...

This shold be very simple macro to do this but I am not sure how to get started by selecting all the sheets EXCEPT sheet1



Sheet 1 has all my data, and a macro creates approx 50 other sheets all named differently with respect to the data in sheet1. I then just need to Sum column E for all 50 sheets.

[eighty2scrambler]

I now see I could use

ActiveWorkbook.sheets.Select

but that gets all the sheets. When in turn i need ALL The sheets, except sheet1

[Shampoo Monkey]

Hey eighty2scrambler,

Try this:

Please Login or Register  to view this content.

[eighty2scrambler]

Thanks !!!!
I used the below. But your idea is exactly what I needed.!


Sub SelectAllButFirst()
Dim wSht As Worksheet

For Each wSht In Worksheets
If wSht.Name <> "Sheet1" Then
wSht.Select (False)
End If
Next wSht
End Sub

[DonkeyOte]

Please encase any/all VBA within CODE tags per Forum rules (and as illustrated in Shampoo Monkey's post)

Re: name testing, ie:

Please Login or Register  to view this content.

it's worth being aware of the fact that by default VBA (unlike Native XL) is case sensitive so SHEET1 <> Sheet1

In this instance this is not likely to be an issue but generally speaking when comparing strings in VBA it's best to normalise them either by:

a) comparing in common case:

Please Login or Register  to view this content.

or

b) use StrComp function

Please Login or Register  to view this content.

You can also set the entire Module to be case insensitive by adding

Please Login or Register  to view this content.

to the head of the module, however, most would opt to control case sensitivity on an "as and when" required basis rather than applying to a module in it's entirety.

[romperstomper]

You should also be aware that if Sheet1 is the active sheet, then this:

Please Login or Register  to view this content.

will select ALL sheets.

[eighty2scrambler]

just when you thought you knew it all.....

Thanks guys!