These articles are written in our spare time, help us maintain it.

How to quickly list all excel sheets in all open workbooks

A quick list of all the worksheets in all the open workbooks can be obtained like this.

Sub SheetNames()
    Dim wb As Workbook
    Dim ws As Worksheet

    For Each wb In Application.Workbooks
        
        Debug.Print "-------------"
    
        Debug.Print wb.Name
    
        For Each ws In wb.Worksheets
            Debug.Print " ;" & ws.Name
        Next
    Next
End Sub

The ";" was added for quick text import where the workbook name was not in the same column as the worksheet name.