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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

1
Rgonzo1971Commented:
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
Determine the Perfect Price for Your IT Services

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

Roy CoxGroup Finance ManagerCommented:
Ignore mine, just realised we need to create an array
0
ste5anSenior 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
ShumsDistinguished 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fabrice LambertFabrice 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.