# Loop Thru Worksheets

#### keith0528

##### Active Member
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

Sh.Activate

Set Loc = .FindNext(Loc)

Loop
End If
End With
Set Loc = Nothing
Next

End Sub``````

thank you

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
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``````

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

Replies
13
Views
609
Replies
14
Views
810
Replies
21
Views
830
Replies
3
Views
387
Replies
5
Views
392

1,219,957
Messages
6,151,145
Members
451,011
Latest member
Pigdog89

### 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.

### Which adblocker are you using?

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

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