Learn and Create Macros easily - Hide and Unhide sheets

 Welcome to the blog.  Here we will learn and Hide and Unhide the Sheets using VBA Code in a very easy way.

In this blog we will learn how to hide and unhide multiple sheets at one go using the VBA Code. We will be using the excel file used in last blog where we created multiple TABs. We will try to  learn to hide these sheets and unhide these sheets.

The Code will look Like this

You can copy the code from below and use it in your excel. Below code is to Hide the sheets

-----------------

Sub Hide_Sheets()

Tot_Sheet = ActiveWorkbook.Sheets.Count

    For i = 2 To Tot_Sheet

        Sheets(i).Visible = xlSheetHidden

    Next i

End Sub

----------

Below code is to UnHide the sheets

---------

Sub Unhide_Sheets()

    For Each Worksheet In ActiveWorkbook.Worksheets

        Worksheet.Visible = xlSheetVisible

    Next Worksheet

End Sub

---------

Now let us learn how to write the code with explanation

Note: You can use the previous blog to create the tabs with city names.

On your excel press Alt+F11

You will see this screen

Then click on insert-> module

You will see this . This is the place where our code will be written.

This is the module and we need a macro or code  to run this. Let us name this Hide_Sheets. Hence we will write 'Hide_Sheets' and press enter. As soon as we press end enter the 'End Sub' appears.  Our code will be between these Sub and End Sub

To toggle between excel sheet and code we need to press Alt+F11

Now let us try to understand what the code means line by line

Sub Hide_Sheets()

'The code will loop from 2nd sheet to last sheet. We will not hide 1st sheet. 

Tot_Sheet = ActiveWorkbook.Sheets.Count

    For i = 2 To Tot_Sheet

        Sheets(i).Visible = xlSheetHidden ' it will hide sheets one by one untill last sheet

    Next i

       

End Sub

Sub Unhide_Sheets()

'The code will loop from 1st sheet to last sheet.

    For Each Worksheet In ActiveWorkbook.Worksheets 

        Worksheet.Visible = xlSheetVisible ' it will unhide sheets one by one untill last sheet

    Next Worksheet

    

End Sub

Press Alt+F8 to activate the macro. This will bring up this screen. Click on Run Button. It will run the macro and the final spread Hide all the sheets except the 1st sheet.

Now to unhide we will again press Alt+F8 to activate the macro. Select 'Unhide_Sheets' macro and click on Run Button  it will run the macro and the final spread will UnHide all the sheets.

Until next blog, happy learning!!!