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.
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 a blank workbook with three worksheets.