I did solve my query. Here's the entire code.

Sub SheetsScan()
Dim ws As Worksheet
Dim strSearch As String
Dim aCell As Range
Dim sName As Variant
Dim lrC As Long

On Error GoTo Err

strSearch = InputBox("Find For This.", "Find This")

For Each ws In ActiveWorkbook.Worksheets
If ws.Name < "Sheet1" Then
Set aCell = ws.UsedRange.Find(what:=strSearch, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not aCell Is Nothing Then
lrC = aCell.End(xlDown).Row
aCell.Resize(lrC, 1).Copy Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)(2)
Set sName = ws.Range("XFD1")
MsgBox sName
End If

End If
Next '/ ws
Exit Sub
Err:
MsgBox Err.Description
End Sub

Howard