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.
'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
'Continue with next workbook
How to copy the macro to your workbook
Press Alt+F11 to open the VB Editor.
Right click on your workbook in the Project Explorer.
Click "Insert" and then "Module".
Copy macro code.
Click in code module window to see the input prompt.