Lost
 

I am looking for some serious help to automate certain tasks in Excel. I do a
report for a mailroom and I responsible for tracking and updating certain
functions of our department.

Here is the deal, I have three worksheets, worksheet one has the listing of
all employees information (ie, telephone numbers, building #, etc.) in the
company, worksheet two has all the mailstops for the employees, and worksheet
three is my report. my goal is to find a way to pull inoformation from
certain rows in worksheet one, and in two and have them placed in three;
basically copy and paste BUT i want it to be automated since i update
worksheet one when people move to new locations.

Does anyone have any suggests? if so, can you also give an example for
reference? (and NO not to do my work! =0)

Thanks!

Lost
 

Assume column A, begining in row 2 in worksheet 3 has an employee IDs and
these IDs can be found in both worksheets 1 and 2 in column A

Dim rng1 as Range, rng2 as Range, rng3 as Range
Dim cell as Range, cell1 as Range, cell2 as Range
Dim res1 as Variant, res2 as Variant
With worksheets("Sheet3")
set rng3 = .Range(.cells(2,1),.cells(rows.count,1).End(xlup))
End with
set rng1 = worksheets("Sheet1").Columns(1).Cells
set rng2 = worksheets("sheet2").Columns(1).Cells
for each cell in rng3
res1 = Application.Match(cell,rng1,0)
res2 = Application.Match(cell,rng2,0)
if not iserror(res1) then
set cell1 = rng1(res1)
cell.offset(0,1).Value = cell1.Offset(0,6).Value
cell.offset(0,2).value = cell1.Offset(0,10).Value
end if
if not iserror(res2) then
set cell2 = rng2(res2)
cell.offset(0,3).Value = cell2.Offset(0,3).Value
cell.offset(0,4).Value = cell2.Offset(0,5).Value
end if
Next

Hopefully this will give you some ideas.

--
Regards,
Tom Ogilvy


"Lost" wrote in message
...
I am looking for some serious help to automate certain tasks in Excel. I
do a
report for a mailroom and I responsible for tracking and updating certain
functions of our department.

Here is the deal, I have three worksheets, worksheet one has the listing
of
all employees information (ie, telephone numbers, building #, etc.) in the
company, worksheet two has all the mailstops for the employees, and
worksheet
three is my report. my goal is to find a way to pull inoformation from
certain rows in worksheet one, and in two and have them placed in three;
basically copy and paste BUT i want it to be automated since i update
worksheet one when people move to new locations.

Does anyone have any suggests? if so, can you also give an example for
reference? (and NO not to do my work! =0)

Thanks!

Lost
 

Hey Tom! Thanks for the imput and I kinda understand, but let me give you the
setup and then you can kinda walk me through if you can;

Sheet1 contains employee info (ie. Column C is First Name, Column D Last
Name, etc.) by colums and Sheet2 contains columns that contain Mailstop info
(ie. Building, Floors, Pillar #, and Mailstop #) by columns.

Now what i want to do is make Sheet2 create a list of people by Last Name
then First Name at specific Mailstops. So basically I would like to see my
info like this (ie. John Smith at mailstop 1122, John Doe at mailstop 1122);

Mailstop for everyone in that group in cell A1 - (ie. 1122)
Cell A3 would contain the Last Name - (ie. Smith)
Cell B3 would contain the First Name - (ie. John)
Cell A4 would contain the Last Name - (ie. Doe)
Cell B4 would contain the First Name - (ie. John)

And then it would go to the next person at the mailstop with the listing
1122, and so on and so forth. I would also like it to update and remove
people as new or old employees are added or removed to the mailstop. I am SO
grateful for the help you have given so far and look forward to any more
advice on this matter.


"Tom Ogilvy" wrote:

Assume column A, begining in row 2 in worksheet 3 has an employee IDs and
these IDs can be found in both worksheets 1 and 2 in column A

Dim rng1 as Range, rng2 as Range, rng3 as Range
Dim cell as Range, cell1 as Range, cell2 as Range
Dim res1 as Variant, res2 as Variant
With worksheets("Sheet3")
set rng3 = .Range(.cells(2,1),.cells(rows.count,1).End(xlup))
End with
set rng1 = worksheets("Sheet1").Columns(1).Cells
set rng2 = worksheets("sheet2").Columns(1).Cells
for each cell in rng3
res1 = Application.Match(cell,rng1,0)
res2 = Application.Match(cell,rng2,0)
if not iserror(res1) then
set cell1 = rng1(res1)
cell.offset(0,1).Value = cell1.Offset(0,6).Value
cell.offset(0,2).value = cell1.Offset(0,10).Value
end if
if not iserror(res2) then
set cell2 = rng2(res2)
cell.offset(0,3).Value = cell2.Offset(0,3).Value
cell.offset(0,4).Value = cell2.Offset(0,5).Value
end if
Next

Hopefully this will give you some ideas.

--
Regards,
Tom Ogilvy


"Lost" wrote in message
...
I am looking for some serious help to automate certain tasks in Excel. I
do a
report for a mailroom and I responsible for tracking and updating certain
functions of our department.

Here is the deal, I have three worksheets, worksheet one has the listing
of
all employees information (ie, telephone numbers, building #, etc.) in the
company, worksheet two has all the mailstops for the employees, and
worksheet
three is my report. my goal is to find a way to pull inoformation from
certain rows in worksheet one, and in two and have them placed in three;
basically copy and paste BUT i want it to be automated since i update
worksheet one when people move to new locations.

Does anyone have any suggests? if so, can you also give an example for
reference? (and NO not to do my work! =0)

Thanks!

Lost
 

Opps, Tom! Were I wrote "Now what i want to do is make Sheet2" I meant
Sheet3, OK?

"Lost" wrote:

Hey Tom! Thanks for the imput and I kinda understand, but let me give you the
setup and then you can kinda walk me through if you can;

Sheet1 contains employee info (ie. Column C is First Name, Column D Last
Name, etc.) by colums and Sheet2 contains columns that contain Mailstop info
(ie. Building, Floors, Pillar #, and Mailstop #) by columns.

Now what i want to do is make Sheet2 create a list of people by Last Name
then First Name at specific Mailstops. So basically I would like to see my
info like this (ie. John Smith at mailstop 1122, John Doe at mailstop 1122);

Mailstop for everyone in that group in cell A1 - (ie. 1122)
Cell A3 would contain the Last Name - (ie. Smith)
Cell B3 would contain the First Name - (ie. John)
Cell A4 would contain the Last Name - (ie. Doe)
Cell B4 would contain the First Name - (ie. John)

And then it would go to the next person at the mailstop with the listing
1122, and so on and so forth. I would also like it to update and remove
people as new or old employees are added or removed to the mailstop. I am SO
grateful for the help you have given so far and look forward to any more
advice on this matter.


"Tom Ogilvy" wrote:

Assume column A, begining in row 2 in worksheet 3 has an employee IDs and
these IDs can be found in both worksheets 1 and 2 in column A

Dim rng1 as Range, rng2 as Range, rng3 as Range
Dim cell as Range, cell1 as Range, cell2 as Range
Dim res1 as Variant, res2 as Variant
With worksheets("Sheet3")
set rng3 = .Range(.cells(2,1),.cells(rows.count,1).End(xlup))
End with
set rng1 = worksheets("Sheet1").Columns(1).Cells
set rng2 = worksheets("sheet2").Columns(1).Cells
for each cell in rng3
res1 = Application.Match(cell,rng1,0)
res2 = Application.Match(cell,rng2,0)
if not iserror(res1) then
set cell1 = rng1(res1)
cell.offset(0,1).Value = cell1.Offset(0,6).Value
cell.offset(0,2).value = cell1.Offset(0,10).Value
end if
if not iserror(res2) then
set cell2 = rng2(res2)
cell.offset(0,3).Value = cell2.Offset(0,3).Value
cell.offset(0,4).Value = cell2.Offset(0,5).Value
end if
Next

Hopefully this will give you some ideas.

--
Regards,
Tom Ogilvy


"Lost" wrote in message
...
I am looking for some serious help to automate certain tasks in Excel. I
do a
report for a mailroom and I responsible for tracking and updating certain
functions of our department.

Here is the deal, I have three worksheets, worksheet one has the listing
of
all employees information (ie, telephone numbers, building #, etc.) in the
company, worksheet two has all the mailstops for the employees, and
worksheet
three is my report. my goal is to find a way to pull inoformation from
certain rows in worksheet one, and in two and have them placed in three;
basically copy and paste BUT i want it to be automated since i update
worksheet one when people move to new locations.

Does anyone have any suggests? if so, can you also give an example for
reference? (and NO not to do my work! =0)

Thanks!