Loop Thru Worksheets

keith0528

Active Member
Joined
Apr 23, 2009
Messages
250
Greetings,

I have some code that looks for a specific number, in this case "123456", searches for it thru all worksheets then reports on Sheet1 what worksheet each instance was found.

so Sheet1 would have:
"123456" found in Sheet2
"123456" found in Sheet4 etc..

The problem is once i introduced the code to put the focus in Sheet1 the loop loses it's place and gets stuck on sheet2 in an infinite loop. I try to put the focus back to where it was but the loop isn't fooled. Any suggestions would be helpful.

code below:
Code:
Sub FindAndExecute()


Dim Sh As Worksheet
Dim Loc As Range
Dim num As String
Dim ShName As String


Ticket = "123456"


For Each Sh In ThisWorkbook.Worksheets
    With Sh.UsedRange
         
        Set Loc = .Cells.Find(What:=Ticket)
        
        If Not Loc Is Nothing Then
            Do Until Loc Is Nothing
                Sh.Activate
            
                'Go to Sheet1 and report ticket-number and sheetname where found
                ShName = ActiveSheet.Name
                                            
                Sheets("Sheet1").Select
                L_Row = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
                Application.ActiveSheet.Cells(L_Row, 1).Value = Ticket & "  Found in   " & ShName
                
                'Return to previous sheet to keep loop on track
                Sh.Activate
                
                Set Loc = .FindNext(Loc)
               
            Loop
        End If
    End With
    Set Loc = Nothing
Next


End Sub

thank you
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Maybe this

UNTESTED

Code:
Sub FindAndExecute()
Dim Sh As Worksheet, Loc As Range, num As String
Dim ShName As String
Ticket = "123456"
For Each Sh In Worksheets
    If Sh.Name <> "Sheet1" Then
        Sh.Activate
            With Sh.UsedRange
                Set Loc = .Cells.Find(What:=Ticket)
                If Not Loc Is Nothing Then
                        L_Row = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row + 1
                        Sheets("Sheet1").Cells(L_Row, 1).Value = Ticket & "  Found in   " & sh.Name
                End If
            End With
        Set Loc = Nothing
    End If
Next
End Sub
 
Upvote 0
Maybe this

UNTESTED

Code:
Sub FindAndExecute()
Dim Sh As Worksheet, Loc As Range, num As String
Dim ShName As String
Ticket = "123456"
For Each Sh In Worksheets
    If Sh.Name <> "Sheet1" Then
        Sh.Activate
            With Sh.UsedRange
                Set Loc = .Cells.Find(What:=Ticket)
                If Not Loc Is Nothing Then
                        L_Row = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row + 1
                        Sheets("Sheet1").Cells(L_Row, 1).Value = Ticket & "  Found in   " & sh.Name
                End If
            End With
        Set Loc = Nothing
    End If
Next
End Sub

Thank you Michael - that works perfectly.

Keith
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

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
Back
Top