| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Getting a Sheet to Choose to Display Other Sheets | Excel Discussion (Misc queries) | |||
| Switching between sheets in same workbook (Like Alt + Tab & Choose | Excel Discussion (Misc queries) | |||
| choose. | Excel Discussion (Misc queries) | |||
| choose? | Excel Discussion (Misc queries) | |||
| Choose. | New Users to Excel | |||
Linear Mode
