Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup baseball scores in 1 worksheet and have them display in ano | Excel Worksheet Functions | |||
Search/Match between 2 x separate Worksheets and populate result in third worksheet | Excel Discussion (Misc queries) | |||
How do I highlight all lookup formulas in a worksheet in one shot. | Excel Discussion (Misc queries) | |||
Lookup worksheet name in separate workbook | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions |