In this post, I am going to demonstrate how to automatically create a new sheet in the current workbook and list all open workbooks and their sheets using a VBA macro.

The image above shows the new worksheet, it contains the names of the workbooks and corresponding worksheets I have currently open.

Macro code

'Name macro
Sub ListWorkbooks()

'Declare variables and data types
Dim wb As Workbook
Dim ws As Worksheet
Dim i As Single, j As Single

'Create a new worksheet and save to object ws
Set ws = Sheets.Add

'Go through open workbooks
For j = 1 To Workbooks.Count

    'Save workbook name to cell A1 and downwards
    Range("A1").Cells(j, 1) = Workbooks(j).Name
    
    'Iterate through worksheets in given workbook
    For i = 1 To Workbooks(j).Sheets.Count
  
            'Save worksheet names to cell B1 and cells further right
            Range("A1").Cells(j, i + 1) = Workbooks(j).Sheets(i).Name

    'Continue with next worksheet
    Next i

'Continue with next workbook
Next j
End Sub

How to copy the macro to your workbook

1. Press Alt+F11 to open the VB Editor.
2. Press with right mouse button on on your workbook in the Project Explorer.
3. Press with left mouse button on "Insert" and then "Module".
4. Copy macro code.
5. Press with left mouse button on in code module window to see the input prompt.
6. Paste code to code module.
7. Return to Excel.

How to run the macro

1. Go to "Developer" tab on the ribbon.
2. Press with left mouse button on "Macros" button.
3. Select "ListWorkbooks" macro.
4. Press with left mouse button on "Run" button.

Get the Excel file

List-all-open-workbooks-and-corresponding-sheets.xlsm

Macro category

Add or remove a value in a drop down list programmatically
This article demonstrates how to add or remove a value in a regular drop down list based on a list […]

Add values to a regular drop-down list programmatically
In this tutorial, I am going to show you how to add values to a drop down list programmatically in […]

Add values to a two-dimensional table based on conditions [VBA]
This article demonstrates how to place values automatically to a table based on two conditions using a short macro. Cell […]

Excel categories

Comments (3)