Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Ranges Possible?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Ranges Possible?
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 On Sun, 31 Dec 2006 14:12:22 -0500, None wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Ranges Possible?
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)) On Sun, 31 Dec 2006 12:29:08 -0800, Gord Dibben <gorddibbATshawDOTca wrote: 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 On Sun, 31 Dec 2006 14:12:22 -0500, None wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Ranges Possible?
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 -- Regards, Tom Ogilvy "None" wrote in message ... 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)) On Sun, 31 Dec 2006 12:29:08 -0800, Gord Dibben <gorddibbATshawDOTca wrote: 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 On Sun, 31 Dec 2006 14:12:22 -0500, None wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Ranges Possible?
Ahh, ok makes sence. Works like a champ. Thanks again for your help! On Sun, 31 Dec 2006 16:46:29 -0600, "Chip Pearson" wrote: 'Ledgers', 'Macros', and 'Instructions' should be declares As Sheets not As Worksheets. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vba help...ranges in new sheet etc | Excel Programming | |||
How to find ranges in a sheet | Excel Programming | |||
Copying ranges from one sheet to another | Excel Programming | |||
selectively copying ranges from one sheet to second sheet | Excel Programming | |||
Combine ranges from 2 sheet into 3rd | Excel Programming |