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:
- Option Explicit
- Sub LoopThroughWorkSheets()
- Dim wrks As WorkSheet
- For Each wrks In ThisWorkBook.WorkSheets
- MsgBox wrks.Name
- Next wrks
- 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.