Referring To More Than One Sheet 1

[SandraF]

I have multiple sheets in my workbook that will be used in a macro. I have the part of the macro that takes info that I need from one sheet and puts it on another sheet completed. The problem is that i need this same information from multiple sheets. I found code for worksheets(Array("Sheet1","Sheet2"etc) which is helpful, but won't work because I do not know how many sheets I will end up having.

I do know that I will never want to include the sheets labeled, "LOG, SUMMARY,CERT REPORT,CONTRACT BRIEF". IS there a way that I can use some kind of while loop like

while <> the above four names do
insert the sheet name into an array

so i can then do the part of the macro i have completed already?

Any help is greatly appreciated! THANKS

 

[dsi]

You can cycle through the Worksheets collection:

Option Explicit
Option Base 1

Sub GetOtherSheets()
    Dim ws As Worksheet
    Dim wsNameList() As String
    Dim iCount As Integer
    Dim i As Long, sTmp As String
    
    ReDim wsNameList(1)
    iCount = 0
    
    'Find all non-standard sheets
    For Each ws In Worksheets
        Select Case ws.Name
            Case "LOG", "SUMMARY", "AND SO ON..."
                'do nothing
            Case Else   'add name to list
                iCount = iCount + 1
                ReDim Preserve wsNameList(iCount)
                wsNameList(iCount) = ws.Name
        End Select
    Next ws
    
    'display list of sheets
    sTmp = ""
    For i = LBound(wsNameList) To UBound(wsNameList)
        sTmp = sTmp & wsNameList(i) & vbCrLf
    Next i
    MsgBox sTmp
    
End Sub