Choose several sheets in VBA

[Jonsson]

Hi,
I'm trying to learn VBA and I need some help with this:
The code below makes the sheets 4 and more to be selected up to the last
sheet in the workbook.
Can anyone give me help to create a code that select sheet 4 to 9 in a
workbook with more than 9 sheets.
I mean, how to get that array in the workbook?

Dim sheetArray() As Integer
Dim firstSheet As Integer, sheetCount As Integer, x As Integer
firstSheet = 4
sheetCount = ThisWorkbook.WorkSheets.Count - firstSheet
ReDim sheetArray(sheetCount)
For x = 0 To sheetCount
sheetArray(x) = firstSheet + x
Next x
ThisWorkbook.WorkSheets(sheetArray).Select


Thanks in advance!

//Thomas

 

[Norman Jones]

Hi Jonnson.

Try:

ActiveWorkbook.Sheets(Array("Sheet4", "Sheet5", "Sheet6", _
"Sheet7", "Sheet8", "Sheet9")).Select

 

[Tom Ogilvy]

Or more general

ActiveWorkbook.worksheets(Array(4,5,6,7,8,9)).Select

 

[Jonsson]

Hi Norman

Thanks, but that's a way I try not to do.
Actually, I have 100 sheets, imagine the look of that code!
I need a shorter code based on the one I have in this message.

 

[Norman Jones]

Hi Jonnson,

If you do not want to build the array in code, go with Tom's suggestion!

Incidentally, how did:

 

[Tom Ogilvy]

Sub AABB()
Dim sheetArray() As Integer
Dim x as Long
Dim lastSheet as Long
Dim firstSheet as Long
Dim firstSheet As Integer, sheetCount As Integer, x As Integer
firstSheet = 4
lastSheet = 9
if lastSheet > thisWorkbook.Worksheets.count then _
lastSheet = ThisWorkbook.Worksheets.count
ReDim sheetArray(0 to lastsheet - firstsheet)
For x = 0 To Ubound(SheetArray)
sheetArray(x) =FirstSheet + x
Next x
ThisWorkbook.WorkSheets(sheetArray).Select

End Sub

 

[Tom Ogilvy]

Whoops, have some double declarations

Sub AABB()
Dim sheetArray() As Long
Dim x as Long
Dim lastSheet as Long
Dim firstSheet as Long
Dim sheetcount as Long
firstSheet = 4
lastSheet = 9
if lastSheet > thisWorkbook.Worksheets.count then _
lastSheet = ThisWorkbook.Worksheets.count
ReDim sheetArray(0 to lastsheet - firstsheet)
For x = 0 To Ubound(SheetArray)
sheetArray(x) =FirstSheet + x
Next x
ThisWorkbook.WorkSheets(sheetArray).Select

End Sub

 

[Jonsson]

Hi, Norman!

It was just an example, to give you guys a hint of what I meant to do!

And Tom!

Thats the one!! GREAT!!

Thanks both of you, for the effort to help me!

//Thomas

 

[Dana DeLouis]

I'm trying to learn VBA

Just to give you another idea since it wasn't mentioned. This just selects
the sheets.

Sub Demo()
Dim j As Long

'Replace with this one sheet
Sheets(4).Select True

For j = 4 To 9
'Don't replace, but "Add" to Selection
Sheets(j).Select False
Next j
End Sub

Consider using "Worksheets" instead of "Sheets" if you wish to avoid
selecting any "Chart Sheets" and "Excel4 Macro Sheets"

HTH
Dana DeLouis

 

[Dana DeLouis]

Actually, I kind of like this version. The j=4 part returns True / False

Sub Demo()
Dim j As Long
For j = 4 To 9
Worksheets(j).Select (j = 4)
Next j
End Sub

HTH
Dana DeLouis

 

[Jonsson]

Hi Dana,

Thanks for your angle, I'm learning new things all the time.

//Thomas