Searching multiple sheets with almost different names

[scubadivingfool]

Looking at searching specific sheets (lax-1, lax-2, etc) for cells that contain "@" and placing them in column A in Sheets3. After the code searches lax-1 and placing the found cells in Column A in Sheets3 it needs to move onto the next lax-"#" and place the found cells at the next empty cell in sheets3 and so on. There can be 5 to 20 sheets with the name lax-"#"

Not exactly sure how to even start something like this or if its even possible.

Any help is appreciated.

Option Explicit

Sub FindPriceTagInformation()
    Dim FirstAddress As String
    Dim MyArr As Variant
    Dim Rng As Range
    Dim Rcount As Long
    Dim I As Long
    Dim NewSh As Worksheet
    

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Fill in the search Value
    MyArr = Array("@")

    Set NewSh = Sheets("Sheet3")

    With Sheets("lax-1").Range("A1:Z100")

        Rcount = 0

        For I = LBound(MyArr) To UBound(MyArr)

    
            Set Rng = .Find(What:=MyArr(I), _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            
            If Not Rng Is Nothing Then
                FirstAddress = Rng.Address
                Do
                    Rcount = Rcount + 1
                    NewSh.Cells(Rcount, 1).Value = Rng.Value
                    
                    Set Rng = .FindNext(Rng)
                Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
            End If
        Next I
    End With

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

 

[GWteB]

One approach is to modify your procedure in such a way that you can use the procedure multiple times.
The code below isn't quite the way I would do it myself, but it will at least give you insight into the possibilities.

Sub FindPriceTagInformation(ByVal argSourceRange As Range, ByVal argText As String, ByVal argDestinationSheet As Worksheet)
   
   
    Dim FirstAddress As String
    Dim Rng As Range
    Dim Rcount As Long

    If Len(argText) > 0 Then

        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With

        With argSourceRange

            Rcount = argDestinationSheet.Cells(argDestinationSheet.Rows.Count, "A").End(xlUp).Row
            Set Rng = .Find(What:=argText, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
           
            If Not Rng Is Nothing Then
                FirstAddress = Rng.Address
                Do
                    Rcount = Rcount + 1
                    argDestinationSheet.Cells(Rcount, "A").Value = Rng.Value
                   
                    Set Rng = .FindNext(Rng)
                Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
            End If
        End With

        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End If
End Sub

Usage example

Sub scubadivingfool()
   
    Dim SourceSh As Worksheet
    Dim DestSh As Worksheet
   
    Set DestSh = ThisWorkbook.Sheets("Sheet3")
   
    For Each SourceSh In ThisWorkbook.Worksheets
        If StrComp(Left(SourceSh.Name, 4), "lax-", vbTextCompare) = 0 Then
            FindPriceTagInformation SourceSh.Range("A1:Z100"), "@", DestSh
        End If
        VBA.DoEvents
    Next SourceSh
End Sub