select certain worksheets using VBA

Dear Experts:

I would like to run a macro that performs the following action:

Select all the worksheets of the currently active workbook with the exception of worksheets with the following worksheet names:

'Results', 'Basis' and all the sheet starting with 'zz_'

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
Andreas HermleTeam leaderAsked:
Who is Participating?
 
ShumsConnect With a Mentor Distinguished Expert - 2017Commented:
Short and easy :)
Sub SelectSheets()
Dim xWs As Worksheet, sel As Boolean
For Each xWs In ThisWorkbook.Sheets
    If xWs.Name <> "Results" And xWs.Name <> "Basis" Then
        If Not xWs.Name Like "zz_*" Then
            xWs.Select Not sel
            sel = True
        End If
    End If
Next xWs
End Sub

Open in new window

2
 
Roy CoxGroup Finance ManagerCommented:
It's not usually necessary to select sheets. maybe this

Option Explicit

Sub x()
    Dim oWs As Worksheet

    For Each oWs In ThisWorkbook.Worksheets

        Select Case oWs.Name
        Case "Results", "Basis"
        Case Else
            If Left(oWs.Name, 3) <> "zz_" Then oWs.Select
        End Select
    End Sub

Open in new window

0
 
NorieConnect With a Mentor VBA ExpertCommented:
Try this.
Dim ws As Worksheet
Dim arrSheets()
Dim cnt As Long

    For Each ws In ActiveWorkbook.Sheets
        Select Case ws.Name
            Case "Results", "Basis"
                ' do nothing
            Case Else
                If Not ws.Name Like "zz_*" Then
                    ReDim Preserve arrSheets(cnt)
                    arrSheets(cnt) = ws.Name
                    cnt = cnt + 1

                End If
        End Select

    Next ws
    
    If cnt > 0 Then
        ActiveWorkbook.Sheets(arrSheets).Select
    End If

Open in new window

1
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rgonzo1971Connect With a Mentor Commented:
Hi,

pls try
Sub macro()
    For Each sh In Worksheets
        Select Case True
            Case sh.Name = "Results", sh.Name = "Basis", sh.Name Like "zz_*"
            Case Else
                strList = strList & sh.Name & "#"
        End Select
    Next
    If strList = "" Then Exit Sub
    strList = Left(strList, Len(strList) - 1)
    aList = Split(strList, "#")
    Worksheets(aList).Select
End Sub

Open in new window

Regards
0
 
Roy CoxGroup Finance ManagerCommented:
Ignore mine, just realised we need to create an array
0
 
ste5anConnect With a Mentor Senior DeveloperCommented:
E.g.

Option Explicit

Public Sub SellectAllSheets()
  
  Dim Workbook As Excel.Workbook
  Dim Worksheet As Excel.Worksheet
  Dim WorksheetNames() As String
  
  ReDim Preserve WorksheetNames(0)
  Set Workbook = ActiveWorkbook
  For Each Worksheet In Workbook.Sheets
    If Not IgnoreWorksheet(Worksheet.Name) Then
      WorksheetNames(UBound(WorksheetNames())) = Worksheet.Name
      ReDim Preserve WorksheetNames(UBound(WorksheetNames()) + 1)
    End If
  Next Worksheet
  
  ReDim Preserve WorksheetNames(UBound(WorksheetNames()) - 1)
  Workbook.Sheets(WorksheetNames()).Select
  Set Workbook = Nothing

End Sub

Private Function IgnoreWorksheet(AWorksheetName As String) As Boolean

  IgnoreWorksheet = False
  If (AWorksheetName = "Results") Then
    IgnoreWorksheet = True
  ElseIf (AWorksheetName = "Basis") Then
    IgnoreWorksheet = True
  ElseIf (Left(AWorksheetName & "...", 3) = "zz_") Then
    IgnoreWorksheet = True
  End If
  
End Function

Open in new window

0
 
Fabrice LambertConnect With a Mentor Fabrice LambertCommented:
What do you intend to do with those "selected worksheets" ?

Usually, with VBA, it isn't needed to select something (beeing cells, worksheets, graphs or whatever) as it has visual impact (wich is annoying for the end user), it is slow as hell, and a simple interraction (wanted or not) from the end user can ruin your code's behavior.

Refering to the objects you intend to use is by far more efficient and safe.
0
 
Andreas HermleTeam leaderAuthor Commented:
Dear Experts:

thank you very much for your overwhelming suppoirt. Really great.

I just test Shum's code, the last one who posted the shortest one. That one worked. Will try the others later one.

Again, thank you very much.

Regards, Andreas
0
 
Andreas HermleTeam leaderAuthor Commented:
Fabrice,

I am fully aware of what you explain to me. This is an exception for some specific reason.

Thank you very much anyways.

Regards, Andreas
1
 
Andreas HermleTeam leaderAuthor Commented:
Dear all,

I chose the shortest one although all of them work just great. It is very interesting for me to see the different approaches to my task. I can see from all the approaches that you are great programmers. Thank you very much.

Too bad one cannot split points anymore and award points 'only' to the best solution.

Anyway, thank you very much for your great, great support. I really appreciate it.

Regards, andreas
1
All Courses

From novice to tech pro — start learning today.