Searching for data across multiple sheets and copying to one main sheet

Hi there, i'm currently using this macro, which is helping me find data in one sheet and copying it to another. However I now need to search for the data across multiple worksheets and then copy it to one worksheet.

Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim LSearchValue As String

On Error GoTo Err_Execute

LSearchValue = InputBox("Please enter a value to search for.", "Enter value")

Sheets("V.Sat").Select
Range("A1").Select

'Start search in row 4
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 E = LSearchValue, copy entire row to Sheet2
If Range("E" & CStr(LSearchRow)).Value = LSearchValue Then



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

'Paste row into Sheet2 in next row
Sheets("Get Info").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("V.Sat").Select

End If

LSearchRow = LSearchRow + 1

Wend

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

MsgBox "All matching data has been copied."

Basically, once it's finished searching on the v.sat worksheet, it then moves to the q.sat, neither, q.dis and v.dis worksheets.

Any help would be really appreciated.

Had a play around, but still no joy. The macro I have there is something that i lifted and shifted from another site.

Maybe I should look at starting a new macro rather than changing that one. Basically i'm looking for a macro (like the one i've copied), which will allow me to search for a criteria. Excel will then look through the sheets called V.Sat, Q.Sat, Neither, Q.Dis, V.Dis and copy the row which contains that criteria into a sheet called 'Get Info'. It will copy the row beneath the last one and continute until it's found all of the row with that criteria in. Once it's found and copied all of those rows, it will stop.

An alternative approach:

Sub x()

Dim r As Long, rngFound As Range, ws, i As Long, nFind As Long

ws = Array("V.Sat", "Q.Sat", "Neither", "Q.Dis", "V.Dis")

nFind = InputBox("Please enter a value to search for.", "Enter value")

For i = LBound(ws) To UBound(ws)
    With Sheets(ws(i))
        Set rngFound = .Cells(1, 1)
        For r = 1 To WorksheetFunction.CountIf(.Cells, nFind)
            Set rngFound = .Cells.Find(What:=nFind, After:=rngFound, LookIn:=xlValues, Lookat:=xlWhole)
            If Not rngFound Is Nothing Then _
                rngFound.EntireRow.Copy Sheets("Get Info").Cells(Rows.Count, 1).End(xlUp)(2)
        Next r
    End With
Next i

End Sub

I'm getting the following error with this:

Runtime error '13':

Type mismatch

Thanks so much for the help so far by the way!

On which line does it error? Do you know how to check the code using F8? If not, post a small sample workbook.