Sheet Ranges Possible?

[None]

Is it possible to assign sheets to a range instead of cells? I am not
sure to code it, can someone please help? Here is a simple example of
what I would like to do.

Given : Lets say I have 9 worksheets, but only want 3 sheets visible
at a time.

workSheets 1-3 = Range1 (First)
workSheets 4-6 = Range2 (Middle)
workSheets 7-9 = Range3 (Last)

Public Sub SheetMode(ByVal Mode As String)

‘ Turn off screen and events
Application.ScreenUpdating = False
Application.EnableEvents = False

‘ Show all to prevent no sheets visible error
For Each ws In ThisWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws

'Hide unwanted sheets
Select Case Mode
Case "First"
'Code to Hide Middle & Last Sheet Ranges
Case "Middle"
'Code to Hide First & Last Sheet Ranges
Case "Last"
'Code to Hide First & Middle Sheet Ranges
End Select

‘ Turn on screen and events
Application.ScreenUpdating = True
Application.EnableEvents = True

End sub

 

[Gord Dibben]

Option Compare Text
Sub Hide_Shts()
whichone = InputBox("Enter your choice..First, Middle or Last")
Application.Run "SheetMode", whichone
End Sub

Public Sub SheetMode(ByVal Mode As String)

Set First = Worksheets(Array("Sheet1", "Sheet2", "Sheet3"))
Set Middle = Worksheets(Array("Sheet4", "Sheet5", "Sheet6"))
Set Last = Worksheets(Array("Sheet7", "Sheet8", "Sheet9"))

' Turn off screen and events
Application.ScreenUpdating = False
Application.EnableEvents = False

' Show all to prevent no sheets visible error
For Each ws In ThisWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws

'Hide unwanted sheets
Select Case Mode
'Code to Hide Middle & Last Sheet Ranges
Case "First"
Middle.Visible = False
Last.Visible = False
Case "Middle"
'Code to Hide First & Last Sheet Ranges
First.Visible = False
Last.Visible = False
Case "Last"
'Code to Hide First & Middle Sheet Ranges
Middle.Visible = False
First.Visible = False
End Select

' Turn on screen and events
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub


Gord Dibben MS Excel MVP

 

[None]

Excellent. One commnet, as this was a simple

Thanks!

As this was a simplified version of what I am doing, I really have a
lot more Sheets.

I believe I can use this instead (Index instead of sheet name)

Set First = Worksheets(Array(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15))

However, since they are in order, is something like this possible to
set First?

Set First = Worksheets(Array(1…15))
or
Set First = Worksheets(Array(1 to 15))

 

[Tom Ogilvy]

Sub abc()
Dim wks As Sheets
Set first = Worksheets(Evaluate("Transpose(Row(1:5))"))
Set middle = Worksheets(Evaluate("Transpose(Row(6:10))"))
Set last = Worksheets(Evaluate("Transpose(Row(11:15))"))
For Each sh In first: Debug.Print sh.Name: Next
For Each sh In middle: Debug.Print sh.Name: Next
For Each sh In last: Debug.Print sh.Name: Next
End Sub

 

[None]

Ok, just one last question shoudl set me up.

I am having trouble with this code, did I use soemthing improperly?
(Should be able to paste code to a new workbook with 6 sheets to
test,) Getting type mismatch error on set First

<<------------Start Code
' Set Variables Global
Public Ledgers As Worksheets
Public Macros As Worksheets
Public Instructions As Worksheets

Public Sub Workbook_Open()
Dim ws As Worksheet

Set Ledgers = Worksheets(Array(1, 2))
Set Macros = Worksheets(Array(3, 4))
Set Instructions = Worksheets(Array(5, 6))

For Each ws In Ledgers
MsgBox ws.Index
Next ws

' ********** Unhide all workbooks *************
Call SheetMode("Ledgers")
' *********************************************

End Sub

Public Sub SheetMode(ByVal Mode As String)

' Show all to prevent no sheets visible error
For Each ws In ThisWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws

' ** Hide unwanted sheets ********************
Select Case Mode
Case "Ledgers"
Macros.Visible = False
Instructions.Visible = False
Case "Macros"
Ledgers.Visible = False
Instructions.Visible = False
Case "Instructions"
Ledgers.Visible = False
Macros.Visible = False
End Select
' *********************************************
End Sub

 

[Chip Pearson]

'Ledgers', 'Macros', and 'Instructions' should be declares As Sheets not As
Worksheets.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[None]

Ahh, ok makes sence. Works like a champ. Thanks again for your help!