List all open workbooks and corresponding sheets [VBA]
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
- Press Alt+F11 to open the VB Editor.
- Press with right mouse button on on your workbook in the Project Explorer.
- Press with left mouse button on "Insert" and then "Module".
- Copy macro code.
- Press with left mouse button on in code module window to see the input prompt.
- Paste code to code module.
- Return to Excel.
How to run the macro
- Go to "Developer" tab on the ribbon.
- Press with left mouse button on "Macros" button.
- Select "ListWorkbooks" macro.
- Press with left mouse button on "Run" button.
Macro category
This article demonstrates how to add or remove a value in a regular drop down list based on a list […]
In this tutorial, I am going to show you how to add values to a drop down list programmatically in […]
This article demonstrates how to place values automatically to a table based on two conditions using a short macro. Cell […]
Excel categories
3 Responses to “List all open workbooks and corresponding sheets [VBA]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
I used this macro to get me the list of all of the open workbooks. I wanted to manipulate it, but I am having trouble and maybe you would have a brilliant answer...
In each of these open workbooks there is a sheet named "Subscriber" and I want to copy the data from each of these sheets and place it into the sheet beneath the list that was created from your macro alone. Any suggestions?
thank you.
Thanks Oscar,
The code is working cool! when one excel environment/session is open, how do we change the code to list out the workbooks from all sessions.
Regards,
Manish
Hello,
I am having troubling coming up with a VBA code in Excel to rename existing tabs with dates that are listed in cells
Thanks, Vickie