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

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

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

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

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

I added comments to help you understand. Last week I help somebody who had
data to copy from over 100 worksheets. When she got done, she told me it was
going to save her hours of work each week.


Sub finddata()

Const MyPath = "c:\temp"

SummaryRow = 1

First = True
'Dir the first time it is called requires a parameter and returns
' first value found
' then calling dir with no parameter returns additional values
Do
If First = True Then
'Searches for a file name
Filename = Dir(MyPath & "\*.xls")
First = False
Else
'continues searching for files
Filename = Dir()
End If
If Filename < "" Then
'Opens workbook using filename
Workbooks.Open MyPath & "\" & Filename
'Gets last row in column A containing data in workbook called filename
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
'Searches for 2214 in new workbook
For RowCount = 1 To LastRow
If Range("B" & RowCount).Value = 2214 Then
'copies data from opened workbook to this workbook
Range("A" & RowCount).Copy _
Destination:=ThisWorkbook.ActiveSheet. _
Range("A" & SummaryRow)
'Puts the Open workbook into worksheet
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,

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

Thank you Joel,

This is so generous of you. I'll have a play and let you know how I get on.
It won't be straight away though!!

Thank you again
--
Mifty


"Joel" wrote:

I added comments to help you understand. Last week I help somebody who had
data to copy from over 100 worksheets. When she got done, she told me it was
going to save her hours of work each week.


Sub finddata()

Const MyPath = "c:\temp"

SummaryRow = 1

First = True
'Dir the first time it is called requires a parameter and returns
' first value found
' then calling dir with no parameter returns additional values
Do
If First = True Then
'Searches for a file name
Filename = Dir(MyPath & "\*.xls")
First = False
Else
'continues searching for files
Filename = Dir()
End If
If Filename < "" Then
'Opens workbook using filename
Workbooks.Open MyPath & "\" & Filename
'Gets last row in column A containing data in workbook called filename
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
'Searches for 2214 in new workbook
For RowCount = 1 To LastRow
If Range("B" & RowCount).Value = 2214 Then
'copies data from opened workbook to this workbook
Range("A" & RowCount).Copy _
Destination:=ThisWorkbook.ActiveSheet. _
Range("A" & SummaryRow)
'Puts the Open workbook into worksheet
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,

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