You would probably find  it easier to use an autofilter and then (if you need
to)copy the visible rows  to the other sheet.

however I am trying to automate the process, it takes
too long to autofilter every sheet  and then copy and paste it to a
different sheet.

I currently use vba to merge  all sheets, but there has to be a way
of using vba to actually search find  then retrieve  them instead of
merging and then using a formula. So much easier pressing a button.

Get the vba to set the autofilter and thenuse that to do the data  selection.

Here is the VBA code and I have attached a file with worksheets  named: *Merge
and Transfer data.xls*
It has 2 worksheets: "Merged Data" and "Results".

The "Merged Data" sheet  has some sample data. The macro takes input from the
user for the date  info in the format "mm/dd/yy". (You may change this part
of the code to suit your needs). The extracted records are written in
"Results" sheet (This sheet is empty to start with). If the date is not
found in the data, the macro returns the message: "No Records Found for
<date>..."

Sub Extract_From_MergedData()
Sheets("Merged Data").Select
DateStr = InputBox("Enter Date to be searched as ""mm/dd/yy"" format: ", _
"Get Date for extracting Data", Now())
DateSrch = CDate(DateStr)
d = 2
LR = Cells.SpecialCells(xlCellTypeLastCell).Row
LC = Cells(1, 1).End(xlToRight).Column
For r = 2 To LR
If Cells(r, 2) = DateSrch Then
Range(Cells(r, 1), Cells(r, LC)).Copy _
Destination:=Sheets("Results").Cells(d, 1)
d = d + 1
End If
Next r
If d = 2 Then
MsgBox "No Records Found for " & DateStr & "....."
Exit Sub
End If
Range(Cells(1, 1), Cells(1, LC)).Copy _
Destination:=Sheets("Results").Cells(1, 1)
MsgBox "DONE!!..."
End Sub

Having a few problems, cant get it running to find  anything, also
didnt get your samples. Anychance you could send the samples to my
email address?