MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Selecting sheets - Intelligently

Posted by Dan on July 19, 2000 9:25 AM

I'm sorry if this has been done before, But I need a VBA Macro that will select all sheets in a range except 1. Then move to another sheet.

However my macro collapses as it contains too many lines or names i.e. too many sheets. When I looked at the code it had array and the sheet names or some of them.

Is there a way to do this simply.

I've followed this Message board closely but can't seem to find an answer that I can use - so hence now.

Thanks to any one that can help


Posted by David on July 20, 0100 1:51 AM

Not exactly sure what you want to do with these sheets or which ones you want to pick.

This will print up all the sheet name it was supplied by ryan.

you can then simply select the one you want by name.

Try to give a little more detail as to what you want to do. And everyone will gladly offer their advice and support and occasionally a complete solution.
Sub ListSheets
Dim x as Integer
Dim Sheet as Worksheet

x = 0
For Each Sheet in Worksheets
x = x+1
Cells(x,1).Value = Sheet.Name
Next Sheet
End Sub


Posted by Ivan Moala on July 20, 0100 4:01 AM

If I understand you correctly you are getting
something like this;

Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6")).Select

You must have more then sheets then this ??

To select these sheets EXCEPT for 1 then try this

Sub SelectAll_BarOne()
Dim Sht

For Each Sht In ThisWorkbook.Sheets
If Sht.Name <> "ExcludedSheet" Then
Sht.Select False
End If

End Sub

Where ExcludedSheet = the name of the sheet
to exclude from the selection.
The Key here is the selection process
Sht.Select False