Search, copy and paste macro.

Hi, I'm rather new to the whole making macros for excel thing and have got rather stuck on trying to create a macro.

What I am trying to do is create a macro which will search for a word or number which is entered into a dialog box, with the entered word or number I would like for multiple worksheets to be searched and for each instance that the searched item appears the row it is present in to be copied and then finally pasted to a sheet which I will define as "Search" for example.

I hope what I am trying to do comes across clearly enough.

Any help or advice on what I can do will be much appreciated.

Thanks.

Assuming:

1) You will place a search button on your sheet called "Search" to run this macro
2) You want the search sheet to only list the rows of the most recent search
3) You want ALL sheet searched except the "Search" sheet
4) You want ALL cells searched on each sheet
5) You want to see the entire row(s)
6) That sometimes the search string is a "whole cell" value and other times it is a "partial cell" value

Try this:

Option Explicit

Sub SearchSheets()
'Summary:  Search all sheets for a specific string and copy rows back to search sheet
Dim MyStr As String
Dim NR As Long, strPart As Long
Dim wsSrch As Worksheet, wsX As Worksheet
Dim CpyRng As Range, cFIND As Range, cFIRST As Range

MyStr = Application.InputBox("Enter the search string", "Search", "cat", Type:=2)
If MyStr = "False" Then Exit Sub

If MsgBox("Only match cells where the string is the WHOLE cell value?", vbYesNo, "Whole matches only?") = vbYes Then
    strPart = 1
Else
    strPart = 2
End If

Set wsSrch = Sheets("Search")  'error here means the search sheet wasn't found
wsSrch.UsedRange.Offset(1).Clear
NR = 2
On Error Resume Next

For Each wsX In Worksheets
    If wsX.Name <> wsSrch.Name Then
        Set cFIND = wsX.Cells.Find(MyStr, LookIn:=xlValues, LookAt:=strPart)
        If Not cFIND Is Nothing Then
            Set CpyRng = wsX.Range("A" & cFIND.Row)
            Set cFIRST = cFIND
            
            Do
                Set CpyRng = Union(CpyRng, wsX.Range("A" & cFIND.Row))
                Set cFIND = wsX.Cells.FindNext(cFIND)
            Loop Until cFIRST.Address = cFIND.Address
            
            CpyRng.EntireRow.Copy wsSrch.Range("A" & NR)
            NR = NR + CpyRng.Rows.Count
            Set CpyRng = Nothing
            Set cFIRST = Nothing
            Set cFIND = Nothing
        End If
    End If
Next wsX

If wsSrch.Range("A2") = "" Then wsSrch.Range("A2") = "None found"
End Sub