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.
Dim book As Workbook, sheet As Worksheet, text As String2. We want to loop through all open workbooks. To achieve this, add the following code line:
For Each book In Workbooks3. 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: " & vbNewLineNote: 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.Worksheets5. We write the names of the worksheets of a workbook to the variable text.
text = text & sheet.Name & vbNewLine6. Close the second loop.
Next sheet7. Add a white line.
text = text & vbNewLine8. Don’t forget to close the first loop.
Next book9. Finally, we display the variable text using a MsgBox.
MsgBox text10. Test the program. Before you click on the command button, give your worksheets some descriptive names and open another blank workbook.
Result:
