Loop through Books and Sheets

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

Situation:

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.

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

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

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:

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

6. Close the second loop.

7. Add a blank line.

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

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

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.

Result:

If you're new here, welcome to Excel Easy! Join over 1 million monthly Excel learners. You can find popular courses here: Data Analysis in Excel and Excel VBA.