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