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.



One thought on “How To Dynamically Hide Or Unhide Multiple Tabs Using VBA in Excel

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>