Select a range of sheets in Excel VBA

**RyanInt** · 16-01-2010

I am trying to select a range of sheets of my workbook between the "Sheet 3" and "Last Sheet-2". For this, I have this formula:

Code:

Sheets(Array("Sheet3", "Sheet4", "Sheet5", Sheet6")).select

Knowing that the number of sheets is variable, Here is my code with a small loop:

Code:

Dim strRange As String
strRange = "Sheets(Array("
For i = 3 To Sheets.Count - 2
If i > 3 Then strRange = strRange & ","
strRange = strRange & Chr(34) & Sheets(i).Name & Chr(34)
Next i
strRange = strRange & " )).select"

Problem: any fool can solve in 5 min I think. I have 98% of that

=> I am simply trying to execute "strRange" but I have a mega hole to do this.

When I test my formula through

Code:

msgbox strRange

It brings me back well

Code:

Sheets(Array("Sheet3", "Sheet4", "Sheet5", Sheet6" )).select

Can be that string is not the right variable to use?

**void** · 16-01-2010

Try replacing

Code:

Dim strRange As String
strRange = "Sheets(Array("
For i = 3 To Sheets.Count - 2
If i > 3 Then strRange = strRange & ","
strRange = strRange & Chr(34) & Sheets(i).Name & Chr(34)
Next i
strRange = strRange & " )).select"

by

Code:

Dim strRange As String
strRange = ""
For i = 3 To Sheets.Count - 2
  If i > 3 Then strRange = strRange & ","
  strRange = strRange & Chr(34) & Sheets(i).Name & Chr(34)
Next i
Sheets(Array(strRange)).select

**RyanInt** · 16-01-2010

I tried your suggestion but this is the error which I got:

Runtime Error "9":
The index does not belong to the selection.

**DotNetUser** · 16-01-2010

I searched quite different solutions to what seemed so simple, basically, but I also have negative alerts!

Expand on in the "best" expert answers: Create a UserForm, and macros, and functions, and a host of learned (and heavy) polished. And yet, not fully conclusive, for some ...

In short, it seems we can not make a single array by adding elements.

That said, why do you select the sheet block? You might as well treat them (copy, export) separately, 1 after another, far more than simply creating USF 1, and use it efficiently ...

Several lines of macros, and that's all!

**$tatic** · 16-01-2010

Based on a solution found on the net

Code:

prem = True
Dim Sheet As Worksheet
For Each Sheet In ActiveWorkbook.Sheets
 ' HERE is the selection criterion
'----------------------------------------------
    If Sheet.Name <> "Sheet2" Then
 '---------------------------------------------
        If prem Then
        Sheet.Select
        prem = False
        Else
        Sheet.Select Replace:=False
        End If
    End If
Next Sheet

I did not put the correct criterion for selection, you put it.

**DotNetUser** · 16-01-2010

Once you've found the solution, which is clear and simple from net as usual, but one question that will surely ask your readers:

why are you using "prem = True" and not "If prem Then"?

Also why not "If prem = True Then", is it implicit?

**$tatic** · 16-01-2010

I found the select with replace:=false on the net. I do not know this possibility.

I merely add the if prem, because in my tests I found that if a sheet is unwanted "Select" before running the macro, it will be part of the batch, where only select for first elected.

if condition...then
when you put in the if expression as a condition, that condition is true or false, if you put that directly to a boolean true or false, it does the same. I'm not sure how to explain, sorry.