CODE 2 SELECT SHEETS LISTED IN A RANGE

[Faraz A. Qureshi]

What code would select sheets that are named in the range: Sheet1!A1:A10
ignoring blanks cells, if any?

Thanx in advance.

 

[Gary Keramidas]

give this a try

Sub select_sheets()
Dim ws As Worksheet
Dim cell As Range
Set ws = Worksheets("Sheet1")
For Each cell In ws.Range("A1:A10")
On Error Resume Next
If cell.Value > "" Then
Worksheets(cell.Value).Select False
End If
On Error GoTo 0
Next
End Sub

 

[Faraz A. Qureshi]

Thanx Gary,

However, the code keeps the already active sheet to remain in the selected
one.
--
Best Regards,

Faraz

give this a try

Sub select_sheets()
Dim ws As Worksheet
Dim cell As Range
Set ws = Worksheets("Sheet1")
For Each cell In ws.Range("A1:A10")
On Error Resume Next
If cell.Value > "" Then
Worksheets(cell.Value).Select False
End If
On Error GoTo 0
Next
End Sub

 

[Gary Keramidas]

kind of late here, so i'll just make this simple addition to the code

Sub select_sheets()
Dim wsname As String
Dim ws As Worksheet
Dim cell As Range
Dim cntr As Long
cntr = 0
wsname = ActiveSheet.Name
Set ws = Worksheets("Sheet1")
For Each cell In ws.Range("A1:A10")
On Error Resume Next
If cell.Value > "" Then
If cntr = 0 Then
Worksheets(cell.Value).Select
cntr = cntr + 1
ElseIf cell.Value > "" Then
Worksheets(cell.Value).Select False
End If
End If
On Error GoTo 0
Next
End Sub


--

Gary Keramidas
Excel 2003

Thanx Gary,

However, the code keeps the already active sheet to remain in the selected
one.

 

[MichDenis]

On Error Resume Next
With WorkSheets("Sheet1")
.Activate
.Range("A1:A10").SpecialCells(xlCellTypeConstants, 23).Select
End With




"Faraz A. Qureshi" <[email protected]> a écrit dans le message de
groupe de discussion : (e-mail address removed)...
What code would select sheets that are named in the range: Sheet1!A1:A10
ignoring blanks cells, if any?

Thanx in advance.