search specific worksheets within workbook

jeternyn1

New Member
Joined
Jul 26, 2007
Messages
2
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:

Code:
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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Joined
Jul 30, 2006
Messages
3,656
jeternyn1,

Welcome to the board.

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

Have a great day,
Stan
 
Joined
Jul 30, 2006
Messages
3,656
jeternyn1,

Here you go.

The following code has not been tested.

Code:
    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
 

Forum statistics

Threads
1,181,102
Messages
5,928,069
Members
436,586
Latest member
latintxn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top