Hi friends,

I have one sheet(say A)sheet with names of associates. These associates are
again listed in different sheets with the name of a project they are working
in shown in the adjacent column.

Names of associates are identically typed in sheet A and other sheets.

In the first sheet I would like to bring up the project they are working in
by searching different sheets.

Pl help.
Krishna

Try this code. The code will put project names in a worksheet called sub
Associates. column A contains the Associates names on all sheets. The
Project name is in Column B on all sheets except Associates.


sub getprojects()

for each sht in activeworkbook.sheets
if sht.name < "Associates" then
RowCount = 1
Do while sht.range("A" & RowCount) < ""
Associate = sht.range("A" & RowCount)
Project = sht.range("B" & RowCount)
With sheets("Associates")

set c = .Columns("A").Find(what:=Associate, _
lookin:=xlvalues,lookat:=xlwhole)
if c is nothing then
msgbox("Cannot find Associate : " & Associate)
else
'Find LastCol
LastCol = .cells(c.row,Columns.Count).end(xltoleft).Column
.cells(c.row,LastCol + 1) = Project
end if
end with
RowCount = RowCount + 1
Loop
end if
next sht

end sub

"Sai Krishna" wrote:

Hi friends,

I have one sheet(say A)sheet with names of associates. These associates are
again listed in different sheets with the name of a project they are working
in shown in the adjacent column.

Names of associates are identically typed in sheet A and other sheets.

In the first sheet I would like to bring up the project they are working in
by searching different sheets.

Pl help.
Krishna

Hi,

this worked brilliantly!

Thanks a lot.
regards
krishna

"Joel" wrote:

Try this code. The code will put project names in a worksheet called sub
Associates. column A contains the Associates names on all sheets. The
Project name is in Column B on all sheets except Associates.


sub getprojects()

for each sht in activeworkbook.sheets
if sht.name < "Associates" then
RowCount = 1
Do while sht.range("A" & RowCount) < ""
Associate = sht.range("A" & RowCount)
Project = sht.range("B" & RowCount)
With sheets("Associates")

set c = .Columns("A").Find(what:=Associate, _
lookin:=xlvalues,lookat:=xlwhole)
if c is nothing then
msgbox("Cannot find Associate : " & Associate)
else
'Find LastCol
LastCol = .cells(c.row,Columns.Count).end(xltoleft).Column
.cells(c.row,LastCol + 1) = Project
end if
end with
RowCount = RowCount + 1
Loop
end if
next sht

end sub

"Sai Krishna" wrote:

Hi friends,

I have one sheet(say A)sheet with names of associates. These associates are
again listed in different sheets with the name of a project they are working
in shown in the adjacent column.

Names of associates are identically typed in sheet A and other sheets.

In the first sheet I would like to bring up the project they are working in
by searching different sheets.

Pl help.
Krishna

This is similar two what I need to do. Where do i enter the code when I have
adapted it for my situation?
--
ken


"Joel" wrote:

Try this code. The code will put project names in a worksheet called sub
Associates. column A contains the Associates names on all sheets. The
Project name is in Column B on all sheets except Associates.


sub getprojects()

for each sht in activeworkbook.sheets
if sht.name < "Associates" then
RowCount = 1
Do while sht.range("A" & RowCount) < ""
Associate = sht.range("A" & RowCount)
Project = sht.range("B" & RowCount)
With sheets("Associates")

set c = .Columns("A").Find(what:=Associate, _
lookin:=xlvalues,lookat:=xlwhole)
if c is nothing then
msgbox("Cannot find Associate : " & Associate)
else
'Find LastCol
LastCol = .cells(c.row,Columns.Count).end(xltoleft).Column
.cells(c.row,LastCol + 1) = Project
end if
end with
RowCount = RowCount + 1
Loop
end if
next sht

end sub

"Sai Krishna" wrote:

Hi friends,

I have one sheet(say A)sheet with names of associates. These associates are
again listed in different sheets with the name of a project they are working
in shown in the adjacent column.

Names of associates are identically typed in sheet A and other sheets.

In the first sheet I would like to bring up the project they are working in
by searching different sheets.

Pl help.
Krishna