search specific worksheets within workbook

[jeternyn1]

I want to be able to search specific sheets within a workbook to make it more efficient than searching through all sheets since I know some of them contain irrelevant information. The current code I have searches through all sheets within the workbook:

For Each ws In wbSource.Worksheets
                With wbSource.Sheets(ws.Name).Cells
                    
                    Application.FindFormat.NumberFormat = "General"
                    Set curCell = .Find(What:=mySearch, LookIn:=xlValues, LookAt:=xlPart, _
                    MatchCase:=False, SearchOrder:=xlByColumns)
             
                    If Not curCell Is Nothing Then
                        ' do stuff

                    End If
                End With
            Next ws

thanks a lot.

 

[stanleydgromjr]

jeternyn1,

Welcome to the board.

What are the 'specific' sheetnames that you want to search?

Have a great day,
Stan

 

[stanleydgromjr]

jeternyn1,

Here you go.

The following code has not been tested.

    Dim j As Integer
    Dim wksArray As Variant

    'Replace the "Sheet#" with the 'specific' sheetnames
    wksArray = Array("Sheet3", "Sheet7", "Sheet11", "Sheet19")

    For j = LBound(wksArray) To UBound(wksArray)

        'Your code
        With Sheets(wksArray(j)).Cells

            Application.FindFormat.NumberFormat = "General"
            Set curCell = .Find(What:=mySearch, LookIn:=xlValues, LookAt:=xlPart, _
            MatchCase:=False, SearchOrder:=xlByColumns)

            If Not curCell Is Nothing Then
                ' do stuff

            End If
        
        End With

    Next j

Have a great day,
Stan

 

[jeternyn1]

works good, thanks.