How to get all info on a specific name from diferent worksheets t

[llrocs]

I have a worksheet that has names of managers. I need to open a new
worksheet that can merge all the info on one specific manager from any cells/
other worksheets.

this is what I have on the columns
name of manager and comments
A
B
C
I need to open separate tabs that can tell me all the commets that manager
"A" said
all coments manager "b" said and so on.
Can any one help me?
Thanks

 

[JLatham]

Try the following code, placed into a new sheet in the workbook. To put it
into the proper place:
Open the workbook.
Insert a new worksheet and select that sheet.
Right-click on the new sheet's name tab and
Choose [View Code] from the list that pops up.
Copy and paste the code below into the code module presented to you.

How it works: Any time you make a new entry (manager's name) into cell A1
on this new sheet, all of the comments made by that manager from all other
sheets in the workbook will be presented on this new page. It auto-clears.
One "oddity" if you already have name "A" in A1, typing A into it again won't
refresh the list, you'll need to delete the existing A and then type A into
it again to get a refreshed list of all comments made by that manager.

The code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim anySheet As Worksheet
Dim namesList As Range
Dim anyName As Range
Dim sampleName As String

If Target.Address <> "$A$1" Then ' must be absolute reference
Exit Sub
End If
If IsEmpty(Target) Then ' nothing to work with
Exit Sub
End If
Application.EnableEvents = False
'save all uppercase version of the mgr name to test with
sampleName = UCase(Trim(Target))
'clear old entries
If Range("A" & Rows.Count).End(xlUp) > 1 Then
'clear columns A and B starting at row 2 down to
'row with last entry in column A.
Range("A2:" & Range("A" & Rows.Count).End(xlUp). _
Offset(0, 1).Address).Clear
End If
For Each anySheet In Worksheets
If anySheet.Name <> Me.Name Then
Set namesList = _
anySheet.Range("A1:" & anySheet.Range("A" & _
Rows.Count).End(xlUp).Address)
For Each anyName In namesList
If UCase(Trim(anyName)) = sampleName Then
'found an entry, copy it
Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = _
anyName
Range("A" & Rows.Count).End(xlUp).Offset(0, 1) = _
anyName.Offset(0, 1)
End If
Next
End If
Next
Set namesList = Nothing
Application.EnableEvents = True
End Sub

 

[David]

I have need of some code and based on your work here...I thought I would ask
you first before putting this out.

I have an excel workbook with multiple (5) sheets (by location names, such
as SE, Rockies, etc) that lists first name (Col a), last name (col b) and
email address (col c) on each of the 5 sheets that I use to send merged
emails through Outlook. I normally send an email to just one group at a time.
At the end of each list are 2-3 duplicated names of office staff so they get
CC of emails that I send out to each individual group.
What I would like to create is a sheet at the end with ALL the names &
emails of everyone on the first 5 sheets so I can send a combined email to
everyone at once.
I would NOT want to duplicate any names (such as the office staff) and would
want any changes, additions, deletions to automatically update the page with
everyone on it.
Can you help me with this? Thanks much in advance.

David

Try the following code, placed into a new sheet in the workbook. To put it
into the proper place:
Open the workbook.
Insert a new worksheet and select that sheet.
Right-click on the new sheet's name tab and
Choose [View Code] from the list that pops up.
Copy and paste the code below into the code module presented to you.

How it works: Any time you make a new entry (manager's name) into cell A1
on this new sheet, all of the comments made by that manager from all other
sheets in the workbook will be presented on this new page. It auto-clears.
One "oddity" if you already have name "A" in A1, typing A into it again won't
refresh the list, you'll need to delete the existing A and then type A into
it again to get a refreshed list of all comments made by that manager.

The code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim anySheet As Worksheet
Dim namesList As Range
Dim anyName As Range
Dim sampleName As String

If Target.Address <> "$A$1" Then ' must be absolute reference
Exit Sub
End If
If IsEmpty(Target) Then ' nothing to work with
Exit Sub
End If
Application.EnableEvents = False
'save all uppercase version of the mgr name to test with
sampleName = UCase(Trim(Target))
'clear old entries
If Range("A" & Rows.Count).End(xlUp) > 1 Then
'clear columns A and B starting at row 2 down to
'row with last entry in column A.
Range("A2:" & Range("A" & Rows.Count).End(xlUp). _
Offset(0, 1).Address).Clear
End If
For Each anySheet In Worksheets
If anySheet.Name <> Me.Name Then
Set namesList = _
anySheet.Range("A1:" & anySheet.Range("A" & _
Rows.Count).End(xlUp).Address)
For Each anyName In namesList
If UCase(Trim(anyName)) = sampleName Then
'found an entry, copy it
Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = _
anyName
Range("A" & Rows.Count).End(xlUp).Offset(0, 1) = _
anyName.Offset(0, 1)
End If
Next
End If
Next
Set namesList = Nothing
Application.EnableEvents = True
End Sub

I have a worksheet that has names of managers. I need to open a new
worksheet that can merge all the info on one specific manager from any cells/
other worksheets.

this is what I have on the columns
name of manager and comments
A
B
C
I need to open separate tabs that can tell me all the commets that manager
"A" said
all coments manager "b" said and so on.
Can any one help me?
Thanks

 

[JLatham]

I believe I can help with that. But I can't get to it for at least 2 days.
I'm unavailable for the next 24-36 hours and have two small projects in the
queue ahead of this one at the moment. And I do keep a queue on these things
when I have to put them aside for the moment. Just let me know if you can
wait that long for a solution by posting here. I won't see the reply until
the Saturday afternoon, but if you say you can wait, then I think I could
have something done before the weekend is over.

I have need of some code and based on your work here...I thought I would ask
you first before putting this out.

I have an excel workbook with multiple (5) sheets (by location names, such
as SE, Rockies, etc) that lists first name (Col a), last name (col b) and
email address (col c) on each of the 5 sheets that I use to send merged
emails through Outlook. I normally send an email to just one group at a time.
At the end of each list are 2-3 duplicated names of office staff so they get
CC of emails that I send out to each individual group.
What I would like to create is a sheet at the end with ALL the names &
emails of everyone on the first 5 sheets so I can send a combined email to
everyone at once.
I would NOT want to duplicate any names (such as the office staff) and would
want any changes, additions, deletions to automatically update the page with
everyone on it.
Can you help me with this? Thanks much in advance.

David

Try the following code, placed into a new sheet in the workbook. To put it
into the proper place:
Open the workbook.
Insert a new worksheet and select that sheet.
Right-click on the new sheet's name tab and
Choose [View Code] from the list that pops up.
Copy and paste the code below into the code module presented to you.

How it works: Any time you make a new entry (manager's name) into cell A1
on this new sheet, all of the comments made by that manager from all other
sheets in the workbook will be presented on this new page. It auto-clears.
One "oddity" if you already have name "A" in A1, typing A into it again won't
refresh the list, you'll need to delete the existing A and then type A into
it again to get a refreshed list of all comments made by that manager.

The code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim anySheet As Worksheet
Dim namesList As Range
Dim anyName As Range
Dim sampleName As String

If Target.Address <> "$A$1" Then ' must be absolute reference
Exit Sub
End If
If IsEmpty(Target) Then ' nothing to work with
Exit Sub
End If
Application.EnableEvents = False
'save all uppercase version of the mgr name to test with
sampleName = UCase(Trim(Target))
'clear old entries
If Range("A" & Rows.Count).End(xlUp) > 1 Then
'clear columns A and B starting at row 2 down to
'row with last entry in column A.
Range("A2:" & Range("A" & Rows.Count).End(xlUp). _
Offset(0, 1).Address).Clear
End If
For Each anySheet In Worksheets
If anySheet.Name <> Me.Name Then
Set namesList = _
anySheet.Range("A1:" & anySheet.Range("A" & _
Rows.Count).End(xlUp).Address)
For Each anyName In namesList
If UCase(Trim(anyName)) = sampleName Then
'found an entry, copy it
Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = _
anyName
Range("A" & Rows.Count).End(xlUp).Offset(0, 1) = _
anyName.Offset(0, 1)
End If
Next
End If
Next
Set namesList = Nothing
Application.EnableEvents = True
End Sub

I have a worksheet that has names of managers. I need to open a new
worksheet that can merge all the info on one specific manager from any cells/
other worksheets.

this is what I have on the columns
name of manager and comments
A
B
C
I need to open separate tabs that can tell me all the commets that manager
"A" said
all coments manager "b" said and so on.
Can any one help me?
Thanks