Select Multiple Sheets In A Vb Code

**Deci** · 09-01-2007, 02:37 PM

I'm trying to select the first 8 sheets of a workbook with 30 sheets, so that I can perform a search operation in all those 8 sheets.
Since the name of those 8 sheets could change, I tryed the following code:
Dim MYARRAY(8) As String
MYARRAY(1) = Sheets(1).Name
MYARRAY(2) = Sheets(2).Name
MYARRAY(3) = Sheets(3).Name
MYARRAY(4) = Sheets(4).Name
MYARRAY(5) = Sheets(5).Name
MYARRAY(6) = Sheets(6).Name
MYARRAY(7) = Sheets(7).Name
MYARRAY(8) = Sheets(8).Name
Sheets(MYARRAY).Select
The problem is that when I get to the last statement (Sheets(myarray).Select), I get error 9, "Subscript out of range".
Can somebody , please help? I'm going nuts!

**dominicb** · 09-01-2007, 03:01 PM

Good evening Deci

It doesn't haveto be quite so complicated. How about :

Sheets(Array(1, 2, 3, 4, 5, 6, 7, 8)).Select

HTH

DominicB

**Deci** · 09-01-2007, 03:12 PM

Thank you! I can't belive how simple it was!

**royUK** · 09-02-2007, 03:50 AM

Deci, try using the Macro Recorder, that would have given you the code.

**dominicb** · 09-02-2007, 04:32 AM

Hi royUK

Using the macro recorder will indeed give you the code, however, it will give you the code using the sheets name. However, as the name may well change you would have to refer to them by number.

Nevertheless using the macro recorder is a great tip that many users forget is available as their experience grows - most of us actually start to learn from the macro recorder.

DominicB

**royUK** · 09-02-2007, 04:34 AM

Domenic, I was only pointing out that the Recorder can often give you the basic code needed rather than waiting for a reply on a Forum. Agreed, the code generally needs working on.

Select Multiple Sheets In A Vb Code

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Select Multiple Sheets In A Vb Code

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions