multiple sheet macro

C

Carolina Girl

Help !!! I need a macro that can scan through multiple sheet and tell me what
sheet information in one cell is located then roll to the next cell and
continue till it gets to the bottom of the sheet …. (ie Master has a list of
numbers in column B each number can be found on a sheet (sheet 1 or sheet 2
(there are more sheets) and tell me what sheet it is on). Each number will
only show up once and on one sheet

Master (Sheet 1)
A B
Sheet 2 567
Sheet 1 897
Sheet 2 456
Any help would be great.. Thanks in advance…
 
G

Gary''s Student

How about:

Sub lookForIt()
Dim sh As Worksheet, n As Integer
Sheets("Master").Activate
n = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To n
Sheets("Master").Activate
numbr = Cells(i, "B").Value
For Each sh In Worksheets
If sh.Name <> "Master" Then
sh.Activate
For Each r In ActiveSheet.UsedRange
If r.Value = numbr Then
Sheets("Master").Activate
Cells(i, "A").Value = sh.Name
GoTo excape
End If
Next
End If
Next
excape:
Next
End Sub
 
C

Carolina Girl

It cames back with master on the master page instead of what tab it is also
listed on..
 
G

Gary''s Student

Make sure the master tab is called "Master" (on the tabname) and not "Sheet1"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top