Three Options for the Worksheet's Visible Property
The "Visible" property allows you to:
- display the worksheet (default)
- hide the worksheet
- definitively hide the worksheet (it can then only be displayed using a VBA code or from the VBA editor)
By default, a worksheet is displayed. Its "Visible" property has the value of "-1 - xlSheetVisible":

Hide a worksheet (0 - xlSheetHidden)
To hide a worksheet, right-click on its tab and select "Hide":

Its "Visible" property corresponds to a value of "0 - xlSheetHidden":

You can then re-display the worksheet at any time by clicking on "Unhide" by right-clicking on any tab (or from the VBA editor):

Hide a worksheet (2 - xlSheetVeryHidden)
To prevent the worksheet from displaying in the tabs, select the value of "2 - xlSheetVeryHidden" for the "Visible" property:

The worksheet can then only be re-displayed by modifying its property in the VBA editor or using a VBA code.
Modify the property using a VBA code
To display worksheet 1 and hide worksheet 2, use the following code:
Sub example()
Sheets("Sheet1").Visible = -1
Sheets("Sheet2").Visible = 2
End Sub
You can also enter constants instead of numbers:
Sub example()
Sheets("Sheet1").Visible = xlSheetVisible
Sheets("Sheet2").Visible = xlSheetVeryHidden
End Sub
Be careful not to hide all the sheets in a workbook, as it may result in a VBA error (when in doubt, start by displaying the sheets that should be visible and then hide the others).