Code to hide a group of worksheets

scotthannaford1973

Board Regular
Joined
Sep 27, 2017
Messages
63
Office Version
  1. 2010
Platform
  1. Windows
Hi all and TIA

I have a workbook with a couple of buttons - one that unhides a couple of workheets called "Audit Trail" and "Audit Trail Open", and another that hides the two sheets. These work great and happy with both... but... I also have a large number of worksheets whose names all start with schemes - such as Schemes - 03 Jan 2020, Schemes - 20-Jan-2020 12¦03 etc (basically they are backup copies) and I want to hide them all in one go. So the question is: can the "Closes" code below be changed so that it hides all schemes whose name starts with: schemes -

thanks

CLOSES:

Sub Hide_Audit()
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("AUDIT TRAIL").Select
ActiveSheet.Visible = xlSheetVeryHidden
Sheets("AUDIT TRAIL OPEN").Select
ActiveSheet.Visible = xlSheetVeryHidden
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("ADMIN").Select
MsgBox "Audit Trail Worksheets Closed"
End Sub


OPENS:

Private Sub CommandButton1_Click()
If TextBox1 = "Password" Then
Sheets("AUDIT TRAIL").Visible = True
Sheets("AUDIT TRAIL OPEN").Visible = True
Me.StartUpPosition = 0
Me.Left = Application.Left + (0.5 * Application.Width) - (0.5 * Me.Width)
Me.Top = Application.Top + (0.5 * Application.Height) - (0.5 * Me.Height)
Else
MsgBox "wrong password"
End If
Unload Me
End Sub

Private Sub TextBox1_Change()
End Sub

Private Sub UserForm_Click()
End Sub
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,914
Office Version
  1. 365
Platform
  1. Windows
You can do something like this:
VBA Code:
Sub Hide_Schemes()

    Dim ws As Worksheet

    For Each ws In Worksheets
        ws.Activate
        If Left(UCase(ws.Name), 6) = "SCHEME" Then
            ActiveSheet.Visible = xlSheetVeryHidden
        End If
    Next ws

    MsgBox "Scheme sheets hidden"

End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,833
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub Hide_Audit()
   Dim Ws As Worksheet
   
   For Each Ws In Worksheets
      If Ws.Name Like "schemes*" Then Ws.Visible = xlSheetVeryHidden
   Next Ws
   ActiveWindow.ScrollWorkbookTabs Position:=xlLast
   Sheets("AUDIT TRAIL").Visible = xlSheetVeryHidden
   Sheets("AUDIT TRAIL OPEN").Visible = xlSheetVeryHidden
   ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
   Sheets("ADMIN").Select
   MsgBox "Audit Trail Worksheets Closed"
End Sub
 

scotthannaford1973

Board Regular
Joined
Sep 27, 2017
Messages
63
Office Version
  1. 2010
Platform
  1. Windows
Thanks, both - have used one on one button and the other suggestion on the second button.

cheers!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,914
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad we were able to help!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,364
Messages
5,595,722
Members
414,013
Latest member
tnobbs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top