Loop through Selected worksheets

[JC2710]

Hi

Can anyone tell me how to loop through only Selected / Grouped worksheets in workbook? I have the code below but cant find a selected property in Sheet.

 For Each Sheet In ThisWorkbook.Sheets
        
                    MsgBox Sheet.Name
       
          Next

Thanks

 

[ChrisM]

You need to use the Window object.

Straight from the help file:

For Each sh In Workbooks("BOOK1.XLS").Windows(1).SelectedSheets
    If sh.Name = "Sheet1" Then
        MsgBox "Sheet1 is selected"
        Exit For
    End If
Next

 

[JC2710]

Thanks Chris

Works great! Seems a strange way to have to do it though. I would have thought that each sheet in the workbook would have a selected property!

Anyway. Thanks Again

 

[pgc01]

Hi JC2710

I would have thought that each sheet in the workbook would have a selected property!

Sorry, but that does not agree with the excel object model.

The fact that a worksheet is selected cannot be reflected directly in the worksheet or in the workbook. As you know a workbook can be displayed in several Windows and in each of the Windows you may select a different group of worksheets. That's why this in a Windows property. In Window 1 you can have Sheet1 and Sheet3 grouped, but in Window 2 of the same Workbook you may have only Sheet5 selected. It makes no sense to ask if Sheet1 is selected unless you specify in which window.

You can also use the ActiveWindow:

Dim wsh As Worksheet
 
For Each wsh In ActiveWindow.SelectedSheets
    MsgBox wsh.Name
Next wsh