Hi Joel,

Got cold feet. May try this when I understand a bit more

Many thanks :-)
--
Mifty


"Joel" wrote:

going through multiple workbooks is more complicated. here is sample code
you can use

Sub finddata()

Const MyPath = "c:\temp"

SummaryRow = 1

First = True
Do
If First = True Then
Filename = Dir(MyPath & "\*.xls")
First = False
Else
Filename = Dir()
End If
If Filename < "" Then
Workbooks.Open MyPath & "\" & Filename
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For RowCount = 1 To LastRow
If Range("B" & RowCount).Value = 2214 Then
Range("A" & RowCount).Copy _
Destination:=ThisWorkbook.ActiveSheet.Range("A" &
SummaryRow)
ThisWorkbook.ActiveSheet.Range("B" & SummaryRow) = Filename
SummaryRow = Summary + 1
End If
Next RowCount
End If
Workbooks(Filename).Close
Loop While Filename < ""

End Sub

"Mifty" wrote:

Hi Joel,


Thanks for replying:-) am going to try right now
Is there any way to make the ref number searched for dynamic? i.e I have
around 70 Results workbooks to populate from the Data workbook and hoped that
I could put the ref number to look up in a cell of the Results book and run
macro from each book in turn.
Cheers
--

Mifty


"Joel" wrote:

You don't have to use find. find becomes complicated when you need to find
all the items. this is much simplier code

summaryRow = 1
for RowCount = 1 to 2000
if Range("B" & RowCount).value = 2214 then
'add your code to copy reults
Range("B" & RowCount).copy _
destination:=sheets("Summary").Range("A" & Summaryrow)
Summaryrow = Summary + 1
end if
next RowCount

"Mifty" wrote:

Hi,

I realise that this is a big ask because I've been searching through all the
other posts similar to this question.

Here's what I did. Results Workbook Sheet1 Cell M1 = Ref number = 2214. So I
started recording a macro and copied M1 and put into FIND dialogue box then
opened Data Workbook and repeated the FIND then copied row adjacent to the
cell that had been found to paste into the Results Workbook. Even as I was
recording the macro I realised that I would just be telling the macro to
search for "2214" and probably just copying the range specified as well.

I've been reading though other posts and I can see that it is so much more
complicated than I had imagined and that I would have to specify ranges and
so much more and even pinching ideas here and there would not be enough

If anyone could help with some code I would be more than grateful and if it
could be done in such a way that I could try to figure out what it's doing
that would be brilliant
TIA
--
Mifty