Deselecting Specific Worksheets

Question

I want to select all worksheets (AKA Sheets.Select) and then DESELECT specific sheets.

For instance, I have three sheets, Sheet1, Sheet2, Sheet3.

Through the macro, I will Sheets.Select, selecting Sheet 1 through Sheet 3. Then, I want to be able to DESELECT Sheet2. Is there a macro function for this?

Answer 1

Here is some code you may use as starting point.

Sub SelectExcept()

  Dim wsh As Worksheet

  Dim f As Boolean

  f = True

  For Each wsh In Worksheets

    Select Case wsh.Name

      ' List the exceptions here

      Case "One", "Three", "Four", "Seven"

        ' Skip these

      Case Else

        wsh.Select f

        f = False

    End Select

  Next wsh

End Sub

Answer 2

Here is a subroutine that you can call from your own macro or function that will select all the worksheets not listed as an argument...

Sub SelectSheetsExceptFor(ParamArray ExcludedSheets())

  Dim WS As Worksheet, ValidSheetActivated As Boolean

  For Each WS In Worksheets

    If UBound(Filter(ExcludedSheets, WS.Name, , vbTextCompare)) < 0 Then

      WS.Select Not ValidSheetActivated

      If Not ValidSheetActivated Then ValidSheetActivated = True

    End If

  Next

End Sub

To use this subroutine, just call it from your own code and list the worksheet names you do not want to select as its arguments. For example, let's say you wanted to select all the worksheets except for Sheet2, Sheet4 and Sheet6; just execute this line of code within your own macro or function...

SelectSheetsExceptFor "Sheet2", "Sheet4", "Sheet6"

You should install the above subroutine in a standard Module.

---

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.

Answer 3

But that only allows me to deselect ONE worksheet. I guess my above "for instance" wasn't very good.

Say I have 10 sheets and I need to deselect 4 of them (worksheets that need to be deselected always has the same name), then what?

Answer 4

FIrst select one and then select all but the one you want to avoid:

Sub dural()

Sheets.Select

MsgBox (" ")

Sheets("Sheet1").Select

MsgBox (" ")

Sheets(Array("Sheet1", "Sheet3")).Select

End Sub

---

gsnu201005

Answer 5

See http://www.thecodecage.com/forumz/members-excel-vba-programming/158608-deselect-single-sheet.html