Welcome to another simple #Excel macro in my #MacroMonday blog series. Today I want to show you how to write a simple Excel macro that will list all Excel worksheets in a workbook. This macro is a great time saver and valuable if you have a workbook but are 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.
Table of contents
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 for worksheet visibility. Worksheets can have one of three visible properties.
- Visible. This property is the default, and if you can see a worksheet, then the property is visible.
- Hidden. The Excel user has flagged the worksheet as Hidden from the Excel interface. The user can right-click and select Hide from the pop-up menu or select the Home Tab | Cells Group | Format | Visibility | Hide & Unhide | Hide Sheet. This basic level of Hidden allows worksheets to be made visible or hidden from the worksheet interface easily.
- 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. 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 simple to flag worksheets as Very hidden as long as you have access to the VB Editor. 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.
The worksheets in your Excel workbook are now visible. 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.
Macro Mondays -Creating and Updating Your Personal Macro Workbook
Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
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 need to enter the rest of the code between these two lines.
Sub ListMySheets
End Sub
Declaring Variables.
We need to declare two variables in the macro, which creates a memory container to store these values. The two variables are ws as Worksheet and x as Integer. We are also setting x as the value of 1.
Dim ws As Worksheet
Dim x as Integer
x= 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.
Worksheets.Add(Before:=Worksheets(1)).Name = "List Of Worksheets"
Use The For Next Loop To List Excel Worksheets.
We use the For Next Loop method to loop through each of the worksheets in the Excel workbook and enter the Worksheet’s name into the newly created ‘List Of Worksheets’. Then, Excel inserts the worksheet name in the cell reference of Cell(x, 1). This is the reference to Row 1 as we have declared that x = 1, and Column 1. Thus, 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.
For Each ws In Worksheets
Sheets("List Of Worksheets").Cells(x, 1) = ws.Name
x = x + 1
Next ws
Ending The Macro.
The routine then ends with the End Sub piece of code that was already entered into the module for us when we started to type the name of the Macro.
End Sub
Copy The Code
Sub ListMySheets
'Macro by HowToExcelAtExcel.com
Dim ws As Worksheet
Dim x as Integer
x= 1
Worksheets.Add(Before:=Worksheets(1)).Name = "List Of Worksheets"
For Each ws In Worksheets
Sheets("List Of Worksheets").Cells(x, 1) = ws.Name
x = x + 1
Next ws
End Sub
