select all but certain sheets

[chris9104]

Evening all, is there any way to select all but certain sheets at the same time when the number of sheets and the names will always vary. I can select all of them without a problem, I just cant manage to exclude any.

Alternatively is it possible to select a range of sheets when I don't know how many there will be? eg select sheets first:last like you could do with a formula

 

[Colin Legg]

Hi Chris,

Yes, you can select all the worksheets using the worksheets object collection select method:

Worksheets.Select

To select the worksheets with indexes 1,3,4 you could use:

Worksheets(Array(1, 3, 4)).Select

Incidentally, why do you want to select them - what do you plan to do with them?

Hope that helps

 

[chris9104]

Hi Colin, i'll be looking to print the sheets.

I don't know whether the array will work because I dont know how many sheets there will be. The worksheets are called the same as the range "Names" on one of the sheets if thats any use.

 

[Colin Legg]

Hi Chris,

In that case you don't need to select the sheets.


So, let's recap.

There will be a varying number of sheets and their names may change. You want to print some of them?

How do you know which sheets you want to print?
Is it that you want to print all sheets except for certain ones? If that's the case, then which sheets do you want to exclude?

 

[chris9104]

Thanks Colin, thats correct.

I am wanting to print all sheets except for "Staff Data","First","Last" and "Reference Data"

 

[Colin Legg]

Hi Chris

And will the names of those worksheets change?

 

[chris9104]

Hi Colin

The sheets that I want to exclude will always be called as above

 

[Colin Legg]

Hi Chris,

Something like this should work:

Sub Example()
    Dim strarrToPrint() As String
    Dim lCounter As Long
    Dim lWst As Long
 
    With Worksheets
        For lWst = 1 To .Count
 
            With .Item(lWst)
 
                Select Case .Name
                    Case "Staff Data", "First", "Last", "Reference Data"
                    Case Else
                        If .Visible = xlSheetVisible Then
                            lCounter = lCounter + 1
                            ReDim Preserve strarrToPrint(1 To lCounter)
                            strarrToPrint(lCounter) = .Name
                        End If
                End Select
            End With
        Next lWst
 
        If LenB(Join(strarrToPrint, "")) > 0 Then .Item(strarrToPrint).PrintOut
    End With
 
End Sub

 

[chris9104]

That worked perfectly, thanks Colin