Select sheets based on what is in a range.
 

Let say I have 5 tabs labeled AAA, BBB, CCC, DDD and EEE. In column A
starting in cell A1, I can have the name of 2, 3 or 4 tabs names listed
(number of tabs to select varies). Is there any way to program a macro to
look at the tab names listed in column A and select just those tabs?

Select sheets based on what is in a range.
 

Hi Steve

You can make a array of the the cell values
There is no error check in this example

Sub aa()
Dim arr() As String
Dim N As Integer

N = 0
For Each cell In Columns("A").SpecialCells(xlCellTypeConstants)
N = N + 1
ReDim Preserve arr(1 To N)
arr(N) = cell.Value
Next

ActiveWorkbook.Worksheets(arr).Select

End Sub

--
Regards Ron De Bruin
http://www.rondebruin.nl



"Steve" wrote in message ...
Let say I have 5 tabs labeled AAA, BBB, CCC, DDD and EEE. In column A
starting in cell A1, I can have the name of 2, 3 or 4 tabs names listed
(number of tabs to select varies). Is there any way to program a macro to
look at the tab names listed in column A and select just those tabs?

Select sheets based on what is in a range.
 

Give this a try...

Sub SelectSheets()
Dim rng As Range
Dim rngTabs As Range

Set rngTabs = Range("A1", Cells(Rows.Count, "A").End(xlUp))
On Error Resume Next
For Each rng In rngTabs
Sheets(rng.Value).Select False
Next rng
On Error GoTo 0

End Sub
--
HTH...

Jim Thomlinson


"Steve" wrote:

Let say I have 5 tabs labeled AAA, BBB, CCC, DDD and EEE. In column A
starting in cell A1, I can have the name of 2, 3 or 4 tabs names listed
(number of tabs to select varies). Is there any way to program a macro to
look at the tab names listed in column A and select just those tabs?

Select sheets based on what is in a range.
 

Jim, thanks for the response. The macro works except that it always selects
the tab with the info in column A even when that tab name is not listed. Any
way to fix this.

Thanks, Steve

"Jim Thomlinson" wrote:

Give this a try...

Sub SelectSheets()
Dim rng As Range
Dim rngTabs As Range

Set rngTabs = Range("A1", Cells(Rows.Count, "A").End(xlUp))
On Error Resume Next
For Each rng In rngTabs
Sheets(rng.Value).Select False
Next rng
On Error GoTo 0

End Sub
--
HTH...

Jim Thomlinson


"Steve" wrote:

Let say I have 5 tabs labeled AAA, BBB, CCC, DDD and EEE. In column A
starting in cell A1, I can have the name of 2, 3 or 4 tabs names listed
(number of tabs to select varies). Is there any way to program a macro to
look at the tab names listed in column A and select just those tabs?

Select sheets based on what is in a range.
 

Ron, how could I build in an error check for this example?

"Ron de Bruin" wrote:

Hi Steve

You can make a array of the the cell values
There is no error check in this example

Sub aa()
Dim arr() As String
Dim N As Integer

N = 0
For Each cell In Columns("A").SpecialCells(xlCellTypeConstants)
N = N + 1
ReDim Preserve arr(1 To N)
arr(N) = cell.Value
Next

ActiveWorkbook.Worksheets(arr).Select

End Sub

--
Regards Ron De Bruin
http://www.rondebruin.nl



"Steve" wrote in message ...
Let say I have 5 tabs labeled AAA, BBB, CCC, DDD and EEE. In column A
starting in cell A1, I can have the name of 2, 3 or 4 tabs names listed
(number of tabs to select varies). Is there any way to program a macro to
look at the tab names listed in column A and select just those tabs?

Select sheets based on what is in a range.
 

Hi Steve

First SpecialCells gives a error if there are no xlCellTypeConstants and also
you must test if the sheet names exist

Try this together with the function in a normal module

Sub Test()
Dim arr() As String
Dim N As Integer

N = 0
On Error Resume Next
For Each cell In Columns("A").SpecialCells(xlCellTypeConstants)
If SheetExists(cell.Value) = True Then
N = N + 1
ReDim Preserve arr(1 To N)
arr(N) = cell.Value
End If
Next

If N 0 Then
ActiveWorkbook.Worksheets(arr).Select
End If

End Sub

Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If WB Is Nothing Then Set WB = ThisWorkbook
SheetExists = CBool(Len(WB.Sheets(SName).Name))
End Function



--
Regards Ron De Bruin
http://www.rondebruin.nl



"Steve" wrote in message ...
Ron, how could I build in an error check for this example?

"Ron de Bruin" wrote:

Hi Steve

You can make a array of the the cell values
There is no error check in this example

Sub aa()
Dim arr() As String
Dim N As Integer

N = 0
For Each cell In Columns("A").SpecialCells(xlCellTypeConstants)
N = N + 1
ReDim Preserve arr(1 To N)
arr(N) = cell.Value
Next

ActiveWorkbook.Worksheets(arr).Select

End Sub

--
Regards Ron De Bruin
http://www.rondebruin.nl



"Steve" wrote in message ...
Let say I have 5 tabs labeled AAA, BBB, CCC, DDD and EEE. In column A
starting in cell A1, I can have the name of 2, 3 or 4 tabs names listed
(number of tabs to select varies). Is there any way to program a macro to
look at the tab names listed in column A and select just those tabs?

Select sheets based on what is in a range.
 

Ron, thank you very much, that worked perfectly. Have a good one.

Steve

"Ron de Bruin" wrote:

Hi Steve

First SpecialCells gives a error if there are no xlCellTypeConstants and also
you must test if the sheet names exist

Try this together with the function in a normal module

Sub Test()
Dim arr() As String
Dim N As Integer

N = 0
On Error Resume Next
For Each cell In Columns("A").SpecialCells(xlCellTypeConstants)
If SheetExists(cell.Value) = True Then
N = N + 1
ReDim Preserve arr(1 To N)
arr(N) = cell.Value
End If
Next

If N 0 Then
ActiveWorkbook.Worksheets(arr).Select
End If

End Sub

Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If WB Is Nothing Then Set WB = ThisWorkbook
SheetExists = CBool(Len(WB.Sheets(SName).Name))
End Function



--
Regards Ron De Bruin
http://www.rondebruin.nl



"Steve" wrote in message ...
Ron, how could I build in an error check for this example?

"Ron de Bruin" wrote:

Hi Steve

You can make a array of the the cell values
There is no error check in this example

Sub aa()
Dim arr() As String
Dim N As Integer

N = 0
For Each cell In Columns("A").SpecialCells(xlCellTypeConstants)
N = N + 1
ReDim Preserve arr(1 To N)
arr(N) = cell.Value
Next

ActiveWorkbook.Worksheets(arr).Select

End Sub

--
Regards Ron De Bruin
http://www.rondebruin.nl



"Steve" wrote in message ...
Let say I have 5 tabs labeled AAA, BBB, CCC, DDD and EEE. In column A
starting in cell A1, I can have the name of 2, 3 or 4 tabs names listed
(number of tabs to select varies). Is there any way to program a macro to
look at the tab names listed in column A and select just those tabs?