In this post I am going to demonstrate how to create a new sheet in the current workbook and list all open workbooks and their sheets using visual basic for applications.

Example,

Macro code

Sub ListWorkbooks()
Dim wb As Workbook
Dim ws As Worksheet
Dim i As Single, j As Single
Set ws = Sheets.Add
For j = 1 To Workbooks.Count
    Range("A1").Cells(j, 1) = Workbooks(j).Name
    For i = 1 To Workbooks(j).Sheets.Count
            Range("A1").Cells(j, i + 1) = Workbooks(j).Sheets(i).Name
    Next i
Next j
End Sub

How to copy the macro to your workbook

  1. Open VB Editor, press Alt+F11.
  2. Right click on your workbook in project explorer.
  3. Click "Insert" and "Module".
  4. Click in code module window.
  5. Paste macro code.
  6. Exit to excel.

How to run the macro

  1. Go to "Developer" tab on the ribbon
  2. Click "Macros" button
  3. Select "ListWorkbooks" macro
  4. Click "Run" button

Download excel *.xlsm file

List all open workbooks and corresponding sheets.xlsm