Help with selection code

[Jeff]

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

 

[Tom Ogilvy]

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.

 

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

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

 

[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