vba loop through worksheet get name in Excel

Q159. In Excel, how can I loop through all WorkSheets and get their names using VBA?

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Sub LoopThroughWorkSheets()
  3. Dim wrks As WorkSheet
  4. ''
  5. For Each wrks In ThisWorkBook.WorkSheets
  6. MsgBox wrks.Name
  7. Next wrks
  8. ''
  9. End Sub


a) Line 5 - "For each" can be used for looping through objects / controls in WorkSheet / WorkBook. In this case through the use of "ThisWorkBook.WorkSheets" it is used to loop through WorkSheets.

b) This is very useful when the macro has to loop through all WorkSheets and then process information from each or some of the WorkSheets.


You can find similar Excel Questions and Answer hereunder

1) How can I save a WorkSheet as a new WorkBook using VBA?

2) Import txt file in Excel

3) How can I set the fill color, font color and set number format of cell to date?

4) How can I set up a dynamic named range that expands automatically when new items are added to the list?

5) How can I hide Formula Bar and Headings using VBA?

6) How can I extract First Name and Last Name from a cell that has Full name?

7) I conducted a test and the test scores are available - how can I get Percentile rank of students?

8) How can I shade alternate rows in color using VBA to make it easier to read voluminous data running into hundreds of rows?

9) How can I extract file name from a full path including folder path and file name?

10) Excel 2010 vba replacement of application filesearch in Excel

11) How can worksheet functions be accessed in VBA?

12) How can I get users to select a folder to save the output of my macro?

13) How can I identify all cells with Data Validation in my WorkSheet?

14) How can I add a WorkSheet and name it as required using VBA?

15) Vba list all files in a folder in Excel