#1   Report Post  
Greg B
 
Posts: n/a
Default 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


  #3   Report Post  
Robert McCurdy
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Lookup baseball scores in 1 worksheet and have them display in ano Oceanverd Excel Worksheet Functions 3 May 1st 05 04:44 AM
Search/Match between 2 x separate Worksheets and populate result in third worksheet Alan Bartley Excel Discussion (Misc queries) 1 April 11th 05 05:21 AM
How do I highlight all lookup formulas in a worksheet in one shot. JT Excel Discussion (Misc queries) 7 March 4th 05 11:35 PM
Lookup worksheet name in separate workbook daj32 Excel Discussion (Misc queries) 4 February 2nd 05 11:54 PM
Weekly Transaction Processing Ralph Howarth Excel Worksheet Functions 4 January 19th 05 06:37 AM


All times are GMT +1. The time now is 03:53 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"