Collection of worksheets

Posted by Paul R on July 03, 2001 4:48 AM

Does anybody know if it is possible to create a collection of several selected worksheets in VBA, so that a loop can be created to perform a procedure for each member (ie sheet) in the collection.

Cheers,
Paul.

Posted by Damon Ostrander on July 03, 2001 1:04 PM

Hi Paul,

Yes, an Excel window object has a SelectedSheets property, which is a collection of all the sheets selected in that window. Here's the Microsoft example of its use:

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

If you want to select the sheets from within VBA, that can be done also using the Worksheet object's Select method with the Replace argument set to False.

Happy computing.

Damon

Posted by Paul R on July 04, 2001 1:34 AM

Thanks

Thanks Damon,

That was just what I was looking for and it worked a treat.

Cheers,
Paul.