Select sheets based on what is in a range.

[Guest]

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?

 

[Ron de Bruin]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Ron de Bruin]

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

 

[Guest]

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

Steve