Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Chart Axes in Excel
  • How to create Checklist in Excel
  • How to create Gauge Chart in Excel
  • Understanding Pivot Tables in Excel
  • Conditional Formatting Data bars Examples in Excel

References

  • How to use Excel MATCH Function
  • How to use Excel ROW Function
  • Merge tables with VLOOKUP in Excel
  • Approximate match with multiple criteria in Excel
  • Count rows that contain specific values in Excel

Data Validations

  • Data validation must not exist in list
  • Excel Data validation specific characters only
  • Excel Data validation date in next 30 days
  • Prevent invalid data entering in specific cells
  • Excel Data validation must begin with

Loop through Books and Sheets Excel

by

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

Result:

Post navigation

Previous Post:

How to use Excel CHOOSE Function

Next Post:

Customize Ribbon In Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • How to use Excel OR Function
  • IF function: Description, Usage, Syntax, Examples and Explanation
  • IF with boolean logic in Excel
  • Excel If, Nested If, And/Or Criteria Examples
  • Not Equal To ‘<>‘ operator in Excel

Date Time

  • Convert Excel time to Unix time in Excel
  • DAY function: Description, Usage, Syntax, Examples and Explanation
  • Get last working day in month in Excel
  • Check If Two Dates are same month in Excel
  • Convert text date dd/mm/yy to mm/dd/yy in Excel

Grouping

  • Map text to numbers in Excel
  • How to randomly assign people to groups in Excel
  • How to randomly assign data to groups in Excel
  • Map inputs to arbitrary values in Excel
  • Calculate conditional mode with criteria in Excel

General

  • Split Cell Content Using Text to Columns in Excel
  • How to count total number of cells in a rectangular range in Excel
  • How to Delete Cells, Row and Rows in Excel
  • Zoom Worksheet in Excel
  • Delete Blank Rows at Once in Excel
© 2026 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning