ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Choose several sheets in VBA (https://www.excelbanter.com/excel-programming/311219-choose-several-sheets-vba.html)

Jonsson
Choose several sheets in VBA
 
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
Choose several sheets in VBA
 
Hi Jonnson.

Try:

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

---
Regards,
Norman



"Jonsson" wrote in message
...
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

Tom Ogilvy
Choose several sheets in VBA
 
Or more general

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

--
Regards,
Tom Ogilvy



"Norman Jones" wrote in message
...
Hi Jonnson.

Try:

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

---
Regards,
Norman



"Jonsson" wrote in message
...
H
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

Jonsson
Choose several sheets in VBA
 
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" skrev i meddelandet
...
Hi Jonnson.

Try:

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

---
Regards,
Norman



"Jonsson" wrote in message
...
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
Choose several sheets in VBA
 
Hi Jonnson,

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

Incidentally, how did:

Can anyone give me help to create a code that select sheet 4 to 9

become:

Actually, I have 100 sheets

---
Regards,
Norman



"Jonsson" wrote in message
...
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" skrev i meddelandet
...
Hi Jonnson.

Try:

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

---
Regards,
Norman



"Jonsson" wrote in message
...
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

Tom Ogilvy
Choose several sheets in VBA
 
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

--
Regards,
Tom Ogilvy

"Jonsson" wrote in message
...
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" skrev i meddelandet
...
Hi Jonnson.

Try:

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

---
Regards,
Norman



"Jonsson" wrote in message
...
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

Tom Ogilvy
Choose several sheets in VBA
 
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
--
Regards,
Tom Ogilvy



"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Jonsson" wrote in message
...
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" skrev i meddelandet
...
Hi Jonnson.

Try:

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

---
Regards,
Norman



"Jonsson" wrote in message
...
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

Jonsson
Choose several sheets in VBA
 
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


"Tom Ogilvy" skrev i meddelandet
...
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

--
Regards,
Tom Ogilvy

"Jonsson" wrote in message
...
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" skrev i meddelandet
...
Hi Jonnson.

Try:

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

---
Regards,
Norman



"Jonsson" wrote in message
...
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

Dana DeLouis[_3_]
Choose several sheets in VBA
 
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



"Jonsson" wrote in message
...
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


"Tom Ogilvy" skrev i meddelandet
...
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

--
Regards,
Tom Ogilvy

"Jonsson" wrote in message
...
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" skrev i meddelandet
...
Hi Jonnson.

Try:

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

---
Regards,
Norman



"Jonsson" wrote in message
...
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

Dana DeLouis[_3_]
Choose several sheets in VBA
 
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


"Dana DeLouis" wrote in message
...
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



"Jonsson" wrote in message
...
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


"Tom Ogilvy" skrev i meddelandet
...
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

--
Regards,
Tom Ogilvy

"Jonsson" wrote in message
...
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" skrev i meddelandet
...
Hi Jonnson.

Try:

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

---
Regards,
Norman



"Jonsson" wrote in message
...
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



Jonsson
Choose several sheets in VBA
 
Hi Dana,

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

//Thomas


"Dana DeLouis" skrev i meddelandet
...
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


"Dana DeLouis" wrote in message
...
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



"Jonsson" wrote in message
...
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


"Tom Ogilvy" skrev i meddelandet
...
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

--
Regards,
Tom Ogilvy

"Jonsson" wrote in message
...
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" skrev i meddelandet
...
Hi Jonnson.

Try:

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

---
Regards,
Norman



"Jonsson" wrote in message
...
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


All times are GMT +1. The time now is 04:24 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com