Loop through Books and Sheets Excel

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 white 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 another blank workbook.

Result: