I inserted a new worksheet named "Summary"
But
I got a run time error '424'
Object required.
Highlighted in yellow the below mentioned code line
Sh.Range("B" & c.Row & ":H" & c.Row).Copy Destination:=DestSht.Range("B" &
NewRow)
Especially, about this new modified code: What it will do?


"joel" wrote:


I left Set out of the statement. found a few other problems. this
should work.

from
DestSht = sheets("Summary")

to
Set DestSht = sheets("Summary")


Make sure you add a sheet Summary manually.




VBA Code:
--------------------



Private Sub cmdbtn1_Click()
Dim Sh As Worksheet
Dim FoundIt As Boolean

Set DestSht = Sheets("Summary")
NewRow = 1


d = "A1: A5000"
Let c = txtbx1.Value

If c = "" Then
MsgBox "You haven't typed anything in the Search Box"
Exit Sub
End If

For Each Sh In ActiveWorkbook.Worksheets
If Sh.Name < "Summary" Then
With Sh.Range(d)
Set b = .Find(c, LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows)

If Not b Is Nothing Then
firstAddress = b.Address
FoundIt = True

Do
Sh.Range("B" & c.Row & ":H" & c.Row).Copy _
Destination:=DestSht.Range("B" & NewRow)
DestSht.Range("A" & NewRow) = Sh.Name
NewRow = NewRow + 1

Set b = .FindNext(after:=b)
Loop While Not b Is Nothing And b.Address < firstAddress
End If
End With
End If
Next
If Not (FoundIt) Then
MsgBox "Data not found!!"
End If

End Sub



--------------------


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=181707

Microsoft Office Help

.