copy matching cell's row to main

[Paul Mueller]

This is what i have so far, everything works fine up until a matching
cell is found.
At - destcell.offset.... i get a run time error 91. I believe there is
more in that line than i need. I just can't figure out how to copy the
row from the matching cell and copy the row to "main".

Set lstbx = ListBox1

For Each sht In ThisWorkbook.Worksheets
sht.Activate
With sht
If .Name <> "main" Then
Set matchcell = .Range("B2", .Range("B65536").End(xlUp))
For Each cell In matchcell
If cell = lstbx Then
Set CopyRange = .Range("B2:I" & .Cells _(.Rows.Count,
"A").End(xlUp).Row)
destCell.Offset(0, -1).Resize _(CopyRange.Rows.Count, 1)
_
.Value = .Name
CopyRange.Copy _
Destination:=destCell
Set destCell = MainSht.Cells(MainSht.Rows.Count, "b") _
.End(xlUp).Offset(1, 0)
End If
Next cell
End If
End With
Next sht

 

[Tom Ogilvy]

If cell = lstbx Then
.Range("B2:I" & .Cells _
(.Rows.Count,"A").End(xlUp).Row).Copy _
Destination:=MainSht.Cells(MainSht.Rows.Count, "b") _
.End(xlUp).Offset(1, 0)
End If

Should do what you want.

You only need to designate the top left cell for your destination - Excel
will figure out how big it needs to be.