trying to loop this vba code through all worksheets

[mcgeezer1]

Hello. The following code scans through a worksheet and copies rows who's cells match "Yes" into a new worksheet.
Can anyone please help show me how to modify it to scan through all of the worksheets in the containing workbook instead of just one?
Thank you for any help.

Sub SearchForString()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute


'Start search in row 4
LSearchRow = 16

'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2


While Len(Range("A" & CStr(LSearchRow)).Value) > 0

'If value in column E = "Mail Box", copy entire row to Sheet2
If Range("M" & CStr(LSearchRow)).Value = "Yes" Then

'Select row in Sheet1 to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy
For Each wrksheet In Worksheets
'Paste row into Sheet2 in next row
Sheets("Totals").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Sheet1 to continue searching
Sheets("Revised C 1").Select

End If

LSearchRow = LSearchRow + 1
Next wrksheet
Wend

'Position on cell A3
Application.CutCopyMode = False
Range("A3").Select

MsgBox "All matching data has been copied."

Exit Sub

Err_Execute:
MsgBox "An error occurred."

End Sub

 

[bstory84]

Try this

Sub SearchForString()
Dim WS As Worksheet
Dim rng As Range
Dim emptyrow As Long
'On Error GoTo Err_Execute

For Each WS In Worksheets
    If WS.Name = "Totals" Then
        Else
    Set rng = WS.Range("M:M")
        For Each C In rng
            If C.Value = "yes" Then
                WS.Activate
                Rows(C.Row & ":" & C.Row).Select
                Selection.Copy
                Sheets("Totals").Activate
                emptyrow = WorksheetFunction.CountA(Range("Totals!M:M")) + 1  'finds next empty row
                Worksheets("Totals").Rows(emptyrow & ":" & emptyrow).Select
                ActiveSheet.Paste
            Else
            
            End If
        Next C
    End If
    
Next WS
Worksheets("Totals").Range("A3").Select
MsgBox "All matching data has been copied."
Exit Sub
Err_Execute:
MsgBox "An error occurred."
End Sub

 

[mcgeezer1]

The code gives me an error:
Run-time error '9'
Subscript out of range.

With the following line of code highlighted in yellow:

Worksheets("Totals").Range("A3").Select



Any ideas?

 

[mcgeezer1]

Ok. The Code runs if you initiate the macro from the "Totals" worksheet.
But it only scans through the first worksheet. None of the other six worksheets are checked.
Any help is much appreciated.

 

[bstory84]

I tested it in a worbook with 26 worksheets, it ran thru all and pulled the info. Are the sheets in the same workbook?
are you pasting the code into a the sheets code or into a module?

 

[mcgeezer1]

yes, they are in the same workbook, no it does not see the other sheets. Did this only work for you when you initiate it from the summary worksheet?

 

[bstory84]

I'm confused by the statement initiate it from the summary. When i test it i initiate it from VBA by telling it to run.

How are you specifically running this sub?or should i say how would you like this to be executed.
example:
via shortkeys , commandbutton placed on worksheet(name please)?