CODE 2 SELECT SHEETS LISTED IN A RANGE

  • Thread starter Thread starter Faraz A. Qureshi
  • Start date Start date
F

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.
 
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
 
Thanx Gary,

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

Faraz


Gary Keramidas said:
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
Click to expand...
 
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


Faraz A. Qureshi said:
Thanx Gary,

However, the code keeps the already active sheet to remain in the selected
one.
Click to expand...
 
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.
 
Back
Top