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
Dan
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.
2713.html
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
Dan
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
Next
End Sub
Where ExcludedSheet = the name of the sheet
to exclude from the selection.
The Key here is the selection process
Sht.Select False
HTH
Ivan
