Below we will look at a program in Excel VBA that loops through all open workbooks and worksheets, and displays all the names.


Loop through Books and Sheets Example

Add the following code lines to the command button:

1. First, we declare two objects and one variable. One object of type Workbook we call book, one object of type Worksheet we call sheet, and a variable of type String we call text.

          Dim book As Workbook, sheet As Worksheet, text As String

2. We want to loop through all open workbooks. To achieve this, add the following code line:

          For Each book In Workbooks

3. We write the text “Workbook: “, the name of the workbook, and the text “Worksheets: “” to the variable text.

          text = text & "Workbook: " & book.Name & vbNewLine & "Worksheets: " & vbNewLine

Note: you can use the & operator to concatenate (join) elements. To start a new line, you can use vbNewLine.

4. To loop through all the worksheets of a workbook, add the following code line:

          For Each sheet In book.Worksheets

5. We write the names of the worksheets of a workbook to the variable text.

          text = text & sheet.Name & vbNewLine

6. Close the second loop.

          Next sheet

7. Add a white line.

          text = text & vbNewLine

8. Don’t forget to close the first loop.

          Next book

9. Finally, we display the variable text using a MsgBox.

          MsgBox text

10. Test the program. Before you click on the command button, give your worksheets some descriptive names and open a blank workbook with three worksheets.


Loop through Books and Sheets in Excel VBA