multiple sheet macro
 

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€¦

multiple sheet macro
 

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

--
Gary''s Student - gsnu200838


"Carolina Girl" wrote:

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€¦

multiple sheet macro
 

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

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200838


"Carolina Girl" wrote:

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€¦

multiple sheet macro
 

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


--
Gary''s Student - gsnu200838


"Carolina Girl" wrote:

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

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200838


"Carolina Girl" wrote:

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€¦