It is easy to hide a sheet, you right click the sheet tab and select Hide. Unfortunately, it is just as easy to unhide that sheet once hidden. If you want to hide sheets and make it harder to unhide them, then these macros may help.

In a previous post (link below) I used message boxes to ask whether a sheet should be hidden.

https://a4accounting.com.au/hiding-multiple-sheets-in-excel-using-xlveryhidden/

The macro techniques below are more automated and based on the sheets you select.

There are four separate macros. Some use the xlVeryHidden setting (yes that is a real setting). This stops the user right clicking a sheet tab to unhide it.

The first, and easiest to understand macro is the UnHideAllSheets macro.

Sub UnHideAllSheets()
' This macro unhides all sheets
 
Dim ws As Worksheet
 
For Each ws In Worksheets
    ws.Visible = True
Next ws
 
Set ws = Nothing
 
End Sub

This macro loops though all the worksheets and makes them visible. This makes testing the other macros a lot easier and it is useful when maintaining a file with lots of hidden sheets.

The second macro HideAllOtherSheets is used when you want to select a single sheet and hide all the other sheets.

Sub HideAllOtherSheets()
' This macro hides all but the active sheet
 
Dim ws As Worksheet
 
For Each ws In Worksheets
    If ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetVeryHidden
Next ws
 
Set ws = Nothing
 
End Sub

This macro loops though all the worksheets and hides all but the ActiveSheet.

The third macro HideUnSelectedSheets allows you to multi-select sheets using the Ctrl key and then hide all the other sheets. You select the sheets you want to remain visible, and the rest will be hidden.

Sub HideUnSelectedSheets()
' This macro hides the sheets that aren't selected
 
Dim ws As Worksheet
Dim strNames
 
'capture all selected sheet names
For Each ws In ActiveWindow.selectedSheets
    strNames = strNames & "," & ws.Name & ","
Next ws
 
'check if a sheet is selected - if not hide it
For Each ws In Worksheets
    If InStr(strNames, "," & ws.Name & ",") = 0 Then ws.Visible = xlSheetVeryHidden
Next ws
 
Set ws = Nothing
 
End Sub

This loops through all the selected sheets and creates a text string of all the names. The next loop then checks if each sheet name is in that text string – if it isn’t it is hidden.

Warning: Multi-selecting sheets is dangerous. You may forget you have multiple sheets selected and every change you make to one sheet is made in ALL the other sheets. Make sure you unselect the sheets by right clicking a sheet tab and selecting Ungroup Sheets.

The last macro HideSelectedSheets hides the selected sheets. Since it is possible to select all the sheets, and at least one sheet must be visible, this macro includes On Error Resume Next to ignore the error if all sheets are selected. The last sheet will remain visible if that is the case.

Sub HideSelectedSheets()
' This macro hides the selected sheets
 
Dim ws As Worksheet
 
'at least one sheet must be visible
' the code below will ignore the error
' if all sheets are selected
' the last sheet will remain visible
On Error Resume Next
 
'hide all selected sheets
For Each ws In ActiveWindow.selectedSheets
    ws.Visible = xlSheetVeryHidden
Next ws
 
Set ws = Nothing
 
End Sub

If you are unsure how to use macros check out the blog post below.

https://a4accounting.com.au/how-to-make-the-most-of-excel-macros/