Worksheet lookup
 

I have a workbook with 140+ worksheets in it and I want to have a macro to
look up if the worksheet has the letter "m" in cell "i1". If the worksheet
has this then I would like it to look in column "A" and look up for the
number 2 and then copy the row to a worksheet called "roundm"

Thanks in advance

Greg

sounds like homework
Use a combination of IF and FIND and COPY

--
Don Guillett
SalesAid Software

"Greg B" wrote in message
...
I have a workbook with 140+ worksheets in it and I want to have a macro to
look up if the worksheet has the letter "m" in cell "i1". If the
worksheet
has this then I would like it to look in column "A" and look up for the
number 2 and then copy the row to a worksheet called "roundm"

Thanks in advance

Greg

Hi Greg.
Homework - humbug.
Us NZer's will just have to stick together.


Sub IfEye1isMandA2is2()
Dim wks As Worksheet, i As Long
Names.Add "Here", RefersTo:="=Offset(roundm!$A$1,counta($A:$A),0)"
With Application
For Each wks In Worksheets
If wks.Name < "roundm" Then
If Not .IsError(.Match(2, wks.[A:A], 0)) And wks.Cells(1, 9) = "m" _
Then wks.Cells(.Match(2, wks.[A:A], 0), 1).EntireRow.Copy [here]
End If
Next wks
End With
End Sub


I hope this gives you the idea how this is done, and how to loop through a range to get the other 2's - if needed.
This will only work if you have nothing in roundm's A column, or no blank rows in any data there.


Regards
Robert McCurdy

"Greg B" wrote in message ...
I have a workbook with 140+ worksheets in it and I want to have a macro to
look up if the worksheet has the letter "m" in cell "i1". If the worksheet
has this then I would like it to look in column "A" and look up for the
number 2 and then copy the row to a worksheet called "roundm"

Thanks in advance

Greg