How To Dynamically Hide Or Unhide Multiple Tabs Using VBA in Excel

Consider a situation in which you need to hide & unhide multiple sheets at a time. If someone is hiding & unhiding manually then this will take a lot of time.

In this article, you will learn how to dynamically hide or unhide multiple tabs using VBA code.

 

Q): I want a macro that will hide or unhide multiple tabs at one point of time.

Following is a snapshot of sheet1 in which there will be two dynamic lists i.e. Hide Tabs & Unhide Tabs
img1

 

  • The idea is when we click on Hide or Unhide button then the listed sheets should get hide or unhide

We need to follow the below steps:

  • To get the result we need to launch VB editor
  • Click on Developer tab
  • From Code group select Visual Basic

img2

 

  • Click on Insert then Module

 

img3

 

This will create new module.

Enter the following code in the Module

 

Sub HideTabs()Dim TabNo As DoubleDim LastTab As Double

LastTab = Range(“Hide_TabsDNR”).Count

On Error Resume Next

For TabNo = 2 To LastTab

Sheets(Range(“Hide_TabsDNR”)(TabNo)).Visible = False

Next TabNo

On Error GoTo 0

Sheets(1).Select

End Sub

 

Sub UnHideTabs()

    Dim TabNo As Double

    Dim LastTab As Double

    LastTab = Range(“Hide_TabsDNR”).Count

    On Error Resume Next

    For TabNo = 2 To LastTab

        Sheets(Range(“UnHide_TabsDNR”)(TabNo)).Visible = True

    Next TabNo

    On Error GoTo 0

    Sheets(1).Select

End Sub

 

img4

 

  • We are all set to run the macro, if we click on Hide button then all the listed sheets will be hidden refer below snapshot

img5

 

  • Same way if we click on Unhide button then all the hidden sheets that are mentioned in column D will get unhide.

In this way we can dynamically hide or unhide multiple tabs at one time.

Users are saying about us...

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube