Welcome to another simple #Excel macro in my #MacroMonday blog series. Today I want to show you how to write a really simple Excel macro that will list all worksheets in an Excel workbook. This is a great time saver and is very useful if you have a workbook but unsure how many worksheets you have in it, and what they are named. This macro will list all Visible, Hidden and Very Hidden worksheets in an Excel workbook.
Worksheet Visible Property.
Before we get into writing our simple bit of VBA code, let’s take a quick look and briefly clarify the different options available when it comes to worksheet visibility. Worksheets can have one of three visible properties.
- Visible. This is the default and if you can see a worksheet then its kind of obvious but it is set to visible.
- Hidden. this is where the Excel Suer has flagged the worksheet as Hidden from Excel interface. The user can right-click and select Hide from the pop-up menu or can select the Home Tab | Cells Group | Format | Visibility | Hide & Unhide | Hide Sheet. This basic level of Hidden allows worksheets to easily be made visible or hidden from the worksheet interface.
- Very Hidden. This property of Very Hidden means that the worksheet cannot be seen to e available to Unhide in the usual pop up menu in the Excel interface. In order to see these worksheets again or set to worksheet property to Very Hidden, the user must use the VB Editor. I have included a brief explanation of how to do this below.
Use the VB Editor- Worksheets Very Hidden
It is easy to flag worksheets as Very hidden as long as you have access to the VB Editor. You will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.
This now allows you to see the worksheets that are in your Excel workbook. In order to change the status of a worksheet to very hidden, follow the simple steps below.
- Select the worksheet to hide.
- In the properties window of the VB editor scroll to Visible
- Select 2 – xlSheetVeryHidden
So, back to writing our small VB macro to list all worksheets in our workbook.
Preparing To Write The Macro.
First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result. You then have a choice, you can either create a module to store your code either in your Personal Macro Workbook or in your current workbook. What’s the difference?. If you save the macro in your Personal Macro workbook it will be available for use in any of my Excel workbooks. If you store it in the current workbook then use is restricted to that workbook.
This macro will be useful in the future so I am going to save it in my Personal Macro Workbook.
More About Your Personal Macro Workbook (PMW).
If you want to read more about your Excel PMW then check out my blog posts below.
Starting The Macro.
We need to start off the macro by inserting a New Module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Type Sub then the name of your macro. In this example, I have called it simply ListMySheets. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We simply need to enter the rest of the code between these two lines.
We need to declare two variables in the macro. This just means we get Excel to create a memory container, or get it to allocate memory to store these values. The two variables we are declaring are ws as Worksheet and x as Integer. We are also setting x as the value of 1.
Insert A New Worksheet.
We first insert a new worksheet at the beginning or before any other worksheets in the workbook. I like to place it here as it is like an index or contents page of the workbook. You can choose to place it after all worksheets by changing the Worksheets.Add(Before to Worksheets.Add(After in the VB code.
Use The For Next Loop.
We use the For Next Loop method to loop through each of the worksheets in the Excel workbook, and enter the name of the worksheet into the newly created ‘List Of Worksheets’. The worksheet name is placed in the cell reference of Cell(x, 1). This refers to Row 1 to begin as we have declared that x = 1, and Column 1. Every time excel loops through each worksheet the Row value of 1 increases by 1. Subsequently, each worksheet name is placed in the next row cell down from the previous one.
Ending The Macro.
The routine then ends with the End Sub piece of code. This was already entered into the module for us when started the type the name of the Macro.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below
So, Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.