Help amending Macro to work on multiple sheets

[MrGJEB]

Hello, I am using the following macro to find the text "No" in column G of my worksheet (Titled "IT") and then copy the corresonding row to a sheet titled "Action Plan". I am wondering how can I amend this code so that it will search multiple sheets. All the sheets have the same format as the "IT" sheet, therefore it will always be searching in column G for the text "No"

Thank you in advance for your help.

The code I have so far is:

Sub SearchForString()
Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute

'Start search in row 2
LSearchRow = 2

'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 G = "Mail Box", copy entire row to Sheet2
If Range("G" & CStr(LSearchRow)).Value = "No" Then

'Select row in Sheet1 to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into Sheet2 in next row
Sheets("Action Plan").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("IT").Select

End If

LSearchRow = LSearchRow + 1

Wend

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

MsgBox "Your Action Plan has been created."

Exit Sub

Err_Execute:
MsgBox "An error occurred."

 

[mumps]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim ws As Worksheet
    Dim rng As Range
    For Each ws In Sheets
        If ws.Name <> "Action Plan" Then
            ws.Activate
            LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            For Each rng In Range("G2:G" & LastRow)
                If rng = "No" Then
                    rng.EntireRow.Copy Sheets("Action Plan").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                End If
            Next rng
        End If
    Next ws
    Sheets("Action Plan").Activate
    Application.ScreenUpdating = True
End Sub