Loop through all sheets and selected sheets

The following three procedures loop through all worksheets, all chart sheets, and all sheets in a workbook, respectively, and display their names in the Immediate window.

Sub LoopThroughAllWorksheets()
'To loop through all worksheets in the active workbook
   Dim ws As Worksheet
   With ActiveWorkbook
     For Each ws In .Worksheets
         Debug.Print ws.Name
     Next ws
   End With
End Sub

 

Sub LoopThroughAllChartSheets()
'To loop through all chart sheets in the active workbook
   Dim ch As Chart
   With ActiveWorkbook
     For Each ch In .Charts
         Debug.Print ch.Name
     Next ch
   End With
End Sub

 

Sub LoopThroughAllSheets ()
'To loop through all sheets in ThisWorkbook
   Dim i As Long
   With ThisWorkbook    
     For i = 1 To .Sheets.Count
         Debug.Print .Sheets(i).Name
     Next i
   End With
End Sub

However, sometimes you want to loop through only selected sheets. The following procedure shows an example.

Sub LoopThroughSelectedSheets()
'To loop through all selected sheets in
'the active workbook
   Dim sh As Object  
   For Each sh In ActiveWindow.SelectedSheets
     Debug.Print sh.Name
   Next sh
End Sub