Office Scripts – Hide all sheets except one

Office Scripts - Worksheets

In this post, we’ll cover the Office Script you can use to hide all sheets except one. I’ll share the basic script, along with the options to change the code to your requirements.

Since Power Automate can use Office Scripts, I will also share the reusable code block to use with dynamic flows.

Example Office Script

Enter the following code into the script editor:

function main(workbook: ExcelScript.Workbook)
{

  //Assign worksheet collection to a variable
  let wsArr = workbook.getWorksheets();

  //Create a variable to hold the worksheet name to retain
  let wsKeepName  = workbook.getActiveWorksheet().getName();

  //Loop through all worksheets in the WSArr worksheet collection
  wsArr.forEach(ws => {

    //if the worksheet in the loop is not the worksheet to retain then
    if (ws.getName() != wsKeepName) {

      //Make the worksheet hidden
      ws.setVisibility(ExcelScript.SheetVisibility.hidden);

    };

  });

}

In the sections below, I will show how to modify this script to meet your specific scenario.

Adapting the script

The example above is the Office Script to hide all sheets except the active sheet. There are 2 different settings that we can change:

  • The worksheet to keep visible
  • Making the worksheet hidden or very hidden

Let’s take a look at these two options.

Change the worksheet

To refer to a specific worksheet, we can use one of the following lines of code. The example above uses the active sheet method, but that line of code can be replaced to refer to use the named sheet or by sheet position methods.

Active sheet:

let wsKeepName = workbook.getActiveWorksheet().getName();

Named sheet:

let wsKeepName = "MySheetName"

Replace MySheetName with the name of your sheet.

Nth sheet position:

let wsKeepName = workbook.getWorksheets()[1].getName();

The number in the square brackets refers to the sheet position. Since Office Scripts have a zero base, 0 is the first sheet, 1 is the second sheet, etc.

Change between hidden and very hidden

The example script hides worksheets, which can be made visible by the user with the standard Excel user interface. Instead, we can make sheets very hidden. These are not displayed in the hidden sheet list; they require more advanced methods to be visible again.

Make sheets hidden

ws.setVisibility(ExcelScript.SheetVisibility.hidden);

Make sheets very hidden

ws.setVisibility(ExcelScript.SheetVisibility.veryHidden);
100 Excel VBA Macros

Do you know the fastest way to learn foreign languages?  It is to read, write, speak, and think in that language as often as possible.  Apart from speaking, programming languages are no different.  The more you immerse yourself in that language, the faster you will pick it up.

100 Excel Macros Book

Therefore, what most people like you need is lots of examples that you can practice.  That is why the 100 Excel VBA Macros eBook exists.  It’s the book for all Excel users who want to learn how to read and write Excel macros, save time, and stand out from their peers.  The book contains:

  • 100 example codes to practice reading and writing macros that will embed the language into your thinking.
  • An introduction to macros in Excel to ensure you can implement the VBA code in the book even if you have no prior knowledge.
  • Consistent code layout between examples to enable you to understand the structure and easily customize the code to meet your needs.
  • Downloadable workbook containing all the source code, so the examples can be added to your project to give you the benefit of VBA straight away.

Reusable Office Script for Power Automate

If running an Office Script within Power Automate, we can either write a specific script similar to that shown above or have a reusable dynamic code.

For a dynamic script, the parameters are passed into the script during the flow. This means we only need one code block, which can be called over and over again, but with different parameters.

In the script below, wsKeepName and visibilityType are the parameters which must be passed during the flow.

function main(workbook: ExcelScript.Workbook, wsKeepName: string, 
  visibilityType: string)
{

  //Assign worksheet collection to a variable
  let wsArr = workbook.getWorksheets();

  //Create variable to hold visibility type
  let visibilityTypeScript: ExcelScript.SheetVisibility;

  //Loop through all worksheets in the WSArr worksheet collection
  wsArr.forEach(ws => {

    //if the worksheet in the loop is not the worksheet to retain
    if (ws.getName() != wsKeepName) {

      //Use switch statement to select the visibility type
      switch(visibilityType) {
        case "hidden":
          visibilityTypeScript = ExcelScript.SheetVisibility.hidden;

        case "veryHidden":
          visibilityTypeScript = ExcelScript.SheetVisibility.veryHidden;
      }

      //Make the worksheet hidden
      ws.setVisibility(visibilityTypeScript);
    };

  });

}

To use the script above with Power Automate, the step will look similar to the following:

Hide all worksheets except one in Power Automate

The Script Parameters passed during the flow are:

  • wsKeepName: The name of the worksheet to be retained
  • visibilityType: The text string hidden or veryHidden.

Notes

A few things to take into consideration are:

  • There needs to be at least one visible sheet. If we try to hide the last visible sheet it will cause an error in both the script editor and Power Automate.
  • Before running this script, we may need to make all sheets visible with another script. This ensures the sheet to be hidden is visible before executing this script.
  • From an Office Scripts perspective, getWorksheets() does not include chart sheets. Therefore, after running the code in this post, chart sheets will remain visible.

Related pages which may be useful to you are:



Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Email Address * First Name *

By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.


Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Leave a Reply

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