Loop through worksheets

Question

I am trying to do the searching for some value in all worksheets. However the code below can only do it on the first worksheet. It seems like the active sheet cannot be changed, Please help me on this issue.

Sub Search()

Dim sRange As Range, Rng As Range
Dim Row1 As Integer, Row2 As Integer
Dim FindString As String
Dim WS As Worksheet

Row1 = 13
Row2 = 2

Application.ScreenUpdating = False
FindString = Sheets("Welcome").Range("N10").Value
For Each WS In ThisWorkbook.Worksheets
    If WS.Name <> "Welcome" Then
        WS.Activate
        LastRow2 = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row

        While Row2 < LastRow2
            Set sRange = ActiveSheet.Range(Cells(Row2, 2), Cells(Row2, 9))
            With sRange
                Set Rng = .Find(What:=FindString, After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
                If Not Rng Is Nothing Then
                    Sheets("Welcome").Range("N" & Row1).Value = WS.Name
                    Sheets("Welcome").Range("O" & Row1).Value = Rng.Row
                    Row1 = Row1 + 1
                End If
            End With
            Row2 = Row2 + 1
        Wend

    End If
Next WS
Sheets("Welcome").Activate
Application.ScreenUpdating = True

End Sub

Are you getting an error? If so, in which line? Is there a specific reason why you want to activate the worksheet? If no, I would avoid it. You can do pretty much whatever you need using the `WS` variable which holds whichever worksheet is currently being processed by the `For-Each` loop. — Stavros Jon, Jul 16 '19 at 08:10
Your question is not very clear. The code above will loop through your worksheets and activate all except "Welcome". Note you have `Application.ScreenUpdating = False` so you won't be able to see this code run. Also you activate "Welcome" at the end so when `ScreenUpdating` gets turned back on this is what you see. I would also include `WS.Select` as a safety measure to your approach. — Dean, Jul 16 '19 at 08:11
I believe you'd greatly benefit from this question on [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and Activate in VBA. — Nacorid, Jul 16 '19 at 08:17
There is nothing in your code that searches anything in Worksheets. — Mikku, Jul 16 '19 at 08:20
No error occur but when I do the search, only the value in the first sheet can be sort out. My goal is to sort out all values related to a keyword in all sheets — Dickson, Jul 16 '19 at 08:26
@Dickson, then you need to paste more of your code to figure out what's going on — AAA, Jul 16 '19 at 08:26
You are never resetting `Row2`, assuming all worksheets have roughly the same amount of rows you're only processing the first worksheet. — Nacorid, Jul 16 '19 at 09:03

Loop through worksheets

0 Answers0