Hi All,

I am creating a workbook that has one main page that will have a customer
name and item to be delivered to the customer. the other worksheeets in the
workbook will be labled something like truck1 truck2, etc. The items on the
main pull sheet will be done at one time during the day and then later they
will be assigned a truck number. What I would like to do is put some code
on each truck worksheet on activate so that when someone goes to that
worksheet it will go to the main pull sheet and go thru and pull out all the
ones that have been assigned to that truck.
I am not real familiar with how to pull excel "recordsets", i know how to do
this working in visual basic but am not sure how to go thru an excel list.
The main pull list will vary in lenght each day so i know i need a looping
statement for i = 1 to ?. second question i am not sure is when it pull the
record to put on the worksheet i know that I want it to start on row 5 but
am not sure how to code it so that the next record and so forth fills in the
next line down.
Any help or website that i might look at would be great.

Thanks,


Jeff

There are several ways to do this, but
assumingthe truck name (matching the sheetname) is in column "C"

Private Sub Worksheet_Activate()
dim sh as worksheet, rng as range
Dim cell as Range
Dim rw as Long
set sh = worksheets("Main")
me.Rows("5:65536").Clearcontents
set rng = sh.range(sh.Cells(1,"C"),sh.Cells(rows.count,"C"). End(xlup))
rw = 5
for each cell in rng
if lcase(cell.Value) = lcase(me.name) then
cell.Entirerow.copy Destination:= _
sh.Cells(rw,1)
rw = rw + 1
end if
Next

End Sub

Right click on the sheet tab and select view code, then paste in code like
the above in the resulting module.

--
Regards,
Tom Ogilvy



"Jeff" wrote in message
...
Hi All,

I am creating a workbook that has one main page that will have a customer
name and item to be delivered to the customer. the other worksheeets in
the
workbook will be labled something like truck1 truck2, etc. The items on
the
main pull sheet will be done at one time during the day and then later
they
will be assigned a truck number. What I would like to do is put some code
on each truck worksheet on activate so that when someone goes to that
worksheet it will go to the main pull sheet and go thru and pull out all
the
ones that have been assigned to that truck.
I am not real familiar with how to pull excel "recordsets", i know how to
do
this working in visual basic but am not sure how to go thru an excel list.
The main pull list will vary in lenght each day so i know i need a looping
statement for i = 1 to ?. second question i am not sure is when it pull
the
record to put on the worksheet i know that I want it to start on row 5 but
am not sure how to code it so that the next record and so forth fills in
the
next line down.
Any help or website that i might look at would be great.

Thanks,


Jeff

Hi Tom,

The code is close but something is not quite right. When i go to Truck 1
worksheet it runs the code but what it is doing is replacing the items in my
Main pull list worksheet with the items for Truck 1. I am thinking it might
be because we first Set to Main or I am not setting the Destination right?

I am trying to debug and walk thru to see if i can figure it out. Any
Ideas?

Thanks,

Jeff
"Tom Ogilvy" wrote in message
...
There are several ways to do this, but
assumingthe truck name (matching the sheetname) is in column "C"

Private Sub Worksheet_Activate()
dim sh as worksheet, rng as range
Dim cell as Range
Dim rw as Long
set sh = worksheets("Main")
me.Rows("5:65536").Clearcontents
set rng = sh.range(sh.Cells(1,"C"),sh.Cells(rows.count,"C"). End(xlup))
rw = 5
for each cell in rng
if lcase(cell.Value) = lcase(me.name) then
cell.Entirerow.copy Destination:= _
sh.Cells(rw,1)
rw = rw + 1
end if
Next

End Sub

Right click on the sheet tab and select view code, then paste in code like
the above in the resulting module.

--
Regards,
Tom Ogilvy



"Jeff" wrote in message
...
Hi All,

I am creating a workbook that has one main page that will have a
customer
name and item to be delivered to the customer. the other worksheeets in
the
workbook will be labled something like truck1 truck2, etc. The items on
the
main pull sheet will be done at one time during the day and then later
they
will be assigned a truck number. What I would like to do is put some
code
on each truck worksheet on activate so that when someone goes to that
worksheet it will go to the main pull sheet and go thru and pull out all
the
ones that have been assigned to that truck.
I am not real familiar with how to pull excel "recordsets", i know how
to
do
this working in visual basic but am not sure how to go thru an excel
list.
The main pull list will vary in lenght each day so i know i need a
looping
statement for i = 1 to ?. second question i am not sure is when it pull
the
record to put on the worksheet i know that I want it to start on row 5
but
am not sure how to code it so that the next record and so forth fills in
the
next line down.
Any help or website that i might look at would be great.

Thanks,


Jeff

sh.Cells(rw,1)
should be

me.cells(rw,1)


Private Sub Worksheet_Activate()
dim sh as worksheet, rng as range
Dim cell as Range
Dim rw as Long
set sh = worksheets("Main")
me.Rows("5:65536").Clearcontents
set rng = sh.range(sh.Cells(1,"C"),sh.Cells(rows.count,"C"). End(xlup))
rw = 5
for each cell in rng
if lcase(cell.Value) = lcase(me.name) then
cell.Entirerow.copy Destination:= _
me.Cells(rw,1)
rw = rw + 1
end if
Next

End Sub

--
Regards,
Tom Ogilvy

"Jeff" wrote in message
...
Hi Tom,

The code is close but something is not quite right. When i go to Truck 1
worksheet it runs the code but what it is doing is replacing the items in
my
Main pull list worksheet with the items for Truck 1. I am thinking it
might
be because we first Set to Main or I am not setting the Destination right?

I am trying to debug and walk thru to see if i can figure it out. Any
Ideas?

Thanks,

Jeff
"Tom Ogilvy" wrote in message
...
There are several ways to do this, but
assumingthe truck name (matching the sheetname) is in column "C"

Private Sub Worksheet_Activate()
dim sh as worksheet, rng as range
Dim cell as Range
Dim rw as Long
set sh = worksheets("Main")
me.Rows("5:65536").Clearcontents
set rng = sh.range(sh.Cells(1,"C"),sh.Cells(rows.count,"C"). End(xlup))
rw = 5
for each cell in rng
if lcase(cell.Value) = lcase(me.name) then
cell.Entirerow.copy Destination:= _
sh.Cells(rw,1)
rw = rw + 1
end if
Next

End Sub

Right click on the sheet tab and select view code, then paste in code
like
the above in the resulting module.

--
Regards,
Tom Ogilvy



"Jeff" wrote in message
...
Hi All,

I am creating a workbook that has one main page that will have a
customer
name and item to be delivered to the customer. the other worksheeets
in
the
workbook will be labled something like truck1 truck2, etc. The items
on
the
main pull sheet will be done at one time during the day and then later
they
will be assigned a truck number. What I would like to do is put some
code
on each truck worksheet on activate so that when someone goes to that
worksheet it will go to the main pull sheet and go thru and pull out
all
the
ones that have been assigned to that truck.
I am not real familiar with how to pull excel "recordsets", i know how
to
do
this working in visual basic but am not sure how to go thru an excel
list.
The main pull list will vary in lenght each day so i know i need a
looping
statement for i = 1 to ?. second question i am not sure is when it
pull
the
record to put on the worksheet i know that I want it to start on row 5
but
am not sure how to code it so that the next record and so forth fills
in
the
next line down.
Any help or website that i might look at would be great.

Thanks,


Jeff

Thanks Tom that did the trick, i was close on where the problem was i just
was not putting in the right syntax.

Thanks again for the help,

Jeff

"Tom Ogilvy" wrote in message
...
sh.Cells(rw,1)
should be

me.cells(rw,1)


Private Sub Worksheet_Activate()
dim sh as worksheet, rng as range
Dim cell as Range
Dim rw as Long
set sh = worksheets("Main")
me.Rows("5:65536").Clearcontents
set rng = sh.range(sh.Cells(1,"C"),sh.Cells(rows.count,"C"). End(xlup))
rw = 5
for each cell in rng
if lcase(cell.Value) = lcase(me.name) then
cell.Entirerow.copy Destination:= _
me.Cells(rw,1)
rw = rw + 1
end if
Next

End Sub

--
Regards,
Tom Ogilvy

"Jeff" wrote in message
...
Hi Tom,

The code is close but something is not quite right. When i go to Truck
1
worksheet it runs the code but what it is doing is replacing the items
in
my
Main pull list worksheet with the items for Truck 1. I am thinking it
might
be because we first Set to Main or I am not setting the Destination
right?

I am trying to debug and walk thru to see if i can figure it out. Any
Ideas?

Thanks,

Jeff
"Tom Ogilvy" wrote in message
...
There are several ways to do this, but
assumingthe truck name (matching the sheetname) is in column "C"

Private Sub Worksheet_Activate()
dim sh as worksheet, rng as range
Dim cell as Range
Dim rw as Long
set sh = worksheets("Main")
me.Rows("5:65536").Clearcontents
set rng = sh.range(sh.Cells(1,"C"),sh.Cells(rows.count,"C"). End(xlup))
rw = 5
for each cell in rng
if lcase(cell.Value) = lcase(me.name) then
cell.Entirerow.copy Destination:= _
sh.Cells(rw,1)
rw = rw + 1
end if
Next

End Sub

Right click on the sheet tab and select view code, then paste in code
like
the above in the resulting module.

--
Regards,
Tom Ogilvy



"Jeff" wrote in message
...
Hi All,

I am creating a workbook that has one main page that will have a
customer
name and item to be delivered to the customer. the other worksheeets
in
the
workbook will be labled something like truck1 truck2, etc. The
items
on
the
main pull sheet will be done at one time during the day and then
later
they
will be assigned a truck number. What I would like to do is put
some
code
on each truck worksheet on activate so that when someone goes to
that
worksheet it will go to the main pull sheet and go thru and pull out
all
the
ones that have been assigned to that truck.
I am not real familiar with how to pull excel "recordsets", i know
how
to
do
this working in visual basic but am not sure how to go thru an excel
list.
The main pull list will vary in lenght each day so i know i need a
looping
statement for i = 1 to ?. second question i am not sure is when it
pull
the
record to put on the worksheet i know that I want it to start on row
5
but
am not sure how to code it so that the next record and so forth
fills
in
the
next line down.
Any help or website that i might look at would be great.

Thanks,


Jeff