Excel allows user to hide worksheets which are not required or wanted it to be hidden. As a result, there will be numerous worksheets to be hidden in a single workbook. Sometime, we need to figure out which hidden sheets we want and which should be deleted from the workbook. In such scenario, list down all hidden sheets will be a quite manual task. Obviously, it can consume couple of hours if there are lot of workbooks contains hundred of worksheets in each workbook. So, this task can be automate with few line of excel macro.
Here is the VBA code to list all hidden sheet in the workbook: Option Explicit Sub List_of_Hidden_Worksheet() ' Author: Dreams24 ' Written for VBA Tricks and tips blog ' https://www.vbatricksntips.com Dim strSht As String Dim sht As Worksheet Dim i As Integer strSht = "" i = 1 'For loop to check each worksheets for hidden property For Each sht In ActiveWorkbook.Worksheets If Not Worksheets(sht.Name).Visible Then 'Pass names of hidden sheets into variable to show in message box. comment or delete this if not required strSht = strSht & i & ". " & sht.Name & vbNewLine 'Uncomment below line to get hidden Sheets list in Sheet1 'ActiveWorkbook.Sheets("Sheet1").Range("A" & i).Value = sht.Name i = i + 1 End If Next sht MsgBox "List of hidden sheets as Below: " & vbNewLine & vbNewLine & strSht, vbInformation, "Hidden Worksheets" End Sub