Activate Multiple Worksheets

[Orin]

Hello,
I saw a similar question to mine asked a few days ago, but the answer had no continuity and was useful only to a VB programmer who could fill in the missing pieces. I am a heavy Excel user, but not a VB programmer.

I have a workbook with 120 invoice sheets +/- (the Names and Quantities vary), a “Table of Contents” sheet to these invoice sheets, an “Instructions” sheet, and 5 data sheets (“Master Invoice”, “Overhead Factors”, “Raw Costs”, “Tax Tables”, “and “Client List”).

I want to edit the “Master Invoice” sheet to make cell value changes or make formatting changes in all the other invoice sheets at once, but I want the non-invoice sheets excluded from that operation. In lieu of selecting and grouping the sheets manually, what vb code would exclude the six sheets listed above by not selecting them?

I have been trying to apply the following string, if that is the way to approach the problem, but can't get it to work correctly. If this piece is sound, than I am not setting up the Sub correctly, and I think the latter is the case. Either way I can’t get a handle on it. I’d just like to activate the sheets mentioned and then manually enter data into one of them to change them all, or preferably have them all change to the value of the active cell or range on the “Master Invoice” sheet. Any thoughts will be appreciated.

Thanks, Orin

If ws.Name <> "Table of Contents" Or ws.Name <> "Instructions" Or ws.Name <> "Overhead Factors" Or ws.Name <> "Raw Costs" Or ws.Name <> "Tax Tables" Or ws.Name <> "Client List" Then

 

[Richie(UK)]

Hi Orin,

How about:

Sub Test()
    Dim arrSheets() As String
    Dim ws As Worksheet
    Dim lcnt As Long
    
    lcnt = 0
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Table of Contents", "Instructions", "Overhead Factors", "Raw Costs", _
                "Tax Tables", "Client List"
            Case Else
                lcnt = lcnt + 1
                ReDim Preserve arrSheets(1 To lcnt)
                arrSheets(lcnt) = ws.Name
        End Select
    Next ws
    
    Worksheets(arrSheets).Select

End Sub

HTH

 

[Orin]

Hi Richie!

It’s a thing of beauty, and just what I was searching for. Great piece of work! Many thanks.

-Orin