Grouping worksheets for global format changes
Posted by Stever on June 08, 2001 10:28 AM
I think that I've created yet another mountain out of a mole hill; but, it does work. For instance, I have a series of 15 worksheets in a single workbook and I need to hide and unhide specific rows on each of the sheets. The following VBA works; but, I'm sure that there has to be an easier way. Any thoughts?
****************************
Sub Hide_Detail()
'
' Hide_Detail Macro
' Macro recorded 06/08/2001 by S Mettes
'
Range("A17:A20").Select
Selection.EntireRow.Hidden = True
Range("A23:A26").Select
Selection.EntireRow.Hidden = True
Range("A28:A41").Select
Selection.EntireRow.Hidden = True
Range("A44:A48").Select
Selection.EntireRow.Hidden = True
End Sub
***********************************
Sub Row_Select()
Sheets(Array("Moorhead", "H&OPS", "Kadien")).Select
Sheets("Moorhead").Activate
Hide_Detail
Sheets("H&OPS").Activate
Hide_Detail
Sheets("Kadien").Activate
Hide_Detail
Sheets("Lou's Rollup").Activate
End Sub
*****************************
Sub Unhide_Detail()
'
' Unhide_Detail Macro
' Macro recorded 06/08/2001 by S Mettes
'
Range("A16:A48").Select
Selection.EntireRow.Hidden = False
End Sub
***********************************
Sub Row_Unselect()
Sheets(Array("Moorhead", "H&OPS", "Kadien")).Select
Sheets("Moorhead").Activate
Unhide_Detail
Sheets("H&OPS").Activate
Unhide_Detail
Sheets("Kadien").Activate
Unhide_Detail
Sheets("Lou's Rollup").Activate
End Sub
Posted by IML on June 08, 2001 10:47 AM
Forgive me if I'm stating the obvious, but without vba this could be achieved by selecting the first sheet, holding shift and selecting the last sheet. You could now hide any rows on all of them. Be sure to right on one of sheets and select "ungroup sheets" when you are done.
Posted by Stever on June 08, 2001 10:56 AM
There are mutiple users of this file with varying degrees of worksheet understanding. I'm hoping to make the function as simple as pressing a button.
Stever
Posted by steve w on June 08, 2001 11:47 AM
Re: Grouping worksheets (Have a look how I hide and unhide my sheets)
Hi stever
This is how I do mine and it would help you. I use it to hide and unhide columns on four pages. You should be able to adapt to your needs very easy.
steve w
Private Sub CheckBox1_Click()
Me.Unprotect Password:=""
If CheckBox1.Value = True Then
Range("D1,H1,L1,P1,T1,X1,AB1,AF1,AJ1,AN1,AR1,AV1,AZ1,BD1,BH1,BL1,BP1,BT1,BX1,CB1,CF1,CJ1,CN1,CR1,CV1,CZ1,DD1,DH1,DL1,DP1").EntireColumn.Hidden = False
Sheet2.Range("DD1:DW1,DY1:EA1,EE1:EY1,FA1").EntireColumn.Hidden = False
Sheet3.Range("DD1:DW1,DY1:EA1,EE1:EY1,FA1").EntireColumn.Hidden = False
Sheet4.Range("DD1:DW1,DY1:EA1,EE1:EY1,FA1").EntireColumn.Hidden = False
Else
Range("D1,H1,L1,P1,T1,X1,AB1,AF1,AJ1,AN1,AR1,AV1,AZ1,BD1,BH1,BL1,BP1,BT1,BX1,CB1,CF1,CJ1,CN1,CR1,CV1,CZ1,DD1,DH1,DL1,DP1").EntireColumn.Hidden = True
Sheet2.Range("DD1:DW1,DY1:EA1,EE1:EY1,FA1").EntireColumn.Hidden = True
Sheet3.Range("DD1:DW1,DY1:EA1,EE1:EY1,FA1").EntireColumn.Hidden = True
Sheet4.Range("DD1:DW1,DY1:EA1,EE1:EY1,FA1").EntireColumn.Hidden = True
End If
Me.Protect Password:=""
End Sub
Hide_Detail Macro Macro recorded 06/08/2001 by S Mettes End Sub
Posted by stever on June 08, 2001 11:53 AM
Re: Grouping worksheets (Have a look how I hide and unhide my sheets)
Thank you Steve W
I had totally forgotten about using check boxes.
Hide_Detail Macro Macro recorded 06/08/2001 by S Mettes End Sub
