select certain worksheets using VBA

Andreas Hermle
Andreas Hermle used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Roy CoxGroup Finance Manager

Commented:
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

NorieAnalyst Assistant
Commented:
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

Top Expert 2016
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Roy CoxGroup Finance Manager

Commented:
Ignore mine, just realised we need to create an array
ste5anSenior Developer
Commented:
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

Managing Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
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

Fabrice LambertConsulting
Distinguished Expert 2017
Commented:
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.
Andreas HermleTeam leader

Author

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
Andreas HermleTeam leader

Author

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
Andreas HermleTeam leader

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial