There is a lot that you did not specify, Is the
search data to be found in a single column or just
anywhere in the sheet? What do you want to extract
from the sheet and put in the last sheet? If the
cells have formulas, do you want the formulas to
be carried over to the last sheet? The sample code
below assumes a single occurrence of the search
data per sheet, and that you would want to copy
the row that the search data is found on to the
last sheet. The rows will be put in the last
sheet in the order that they are found.

Sub version()
Dim sNdx As Long, lstSh As Worksheet,lrAs Long
Dim f As Range
sNdx = ThisWorkbook.Sheets.Count
Set lstSh = Sheets(sNdx)
SrchDat = 'Whatever you want to search
For Each sh In ThisWorkbook.Sheets
Set f = Sh.Find(SrchDat, LookIn:=xlValues)
If Not f Is Nothing Then
lr = lstSh.Cells(Rows.Count, 1).End(xlUP).Row
Sh.f.EntireRow.Copy lstSh.Range("A" & lr + 1)
End If
Next
End Sub







"duhrl" wrote:


I have a workbook, about 20 tabs, that once all data has been placed in
them i need to run a macro that pulls from a search criteria. My problem
is with my limited knowledge of macro's and almost nothing of VBA I need
the macro to do nothing if the current tab has none of the items for the
current search. the data from each of the 20 or so pages is all pasted
to a tab at the end of the workbood.

Thanks in advance.


--
duhrl
------------------------------------------------------------------------
duhrl's Profile: http://www.thecodecage.com/forumz/member.php?userid=126
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=62341