View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Otto Moehrbach Otto Moehrbach is offline
external usenet poster
 
Posts: 1,090
Default Recordset from excel worksheet

Damien

If this is a one shot thing, I would just use a For loop to
create the new sheet and copy all the corresponding records to that sheet,
then move on to the next "route".

If this is an ongoing task and you're looking for the most
efficient code, record a macro and use the Auto-Filter feature to filter the
data for the specific "route", then copy the filtered data to the
appropriate sheet.

Post back if you need help with this. HTH Otto

"Damien McBain" wrote in message
...
Otto Moehrbach wrote:

Damien
Provide details about the layout of the table (what is in what
column,
what is in what row?). Also provide details about the layout of the new
sheets (what goes in what column, what goes in what row?). HTH Otto


Thanks Otto

I'm trying to convert one part of a MS Access application I made into an
XL
app because I'm leaving the company and they won't be able to support an
Access app.

The main table, an extract from our ERP (which is now in an XL sheet) has
9
fields and can be up to 12,000 records. The first field in each record is
the "route"

I have another sheet which has one record for each "route". There are 26
routes. The main table consists of multiple records for each route.

Im thinking of using code like:

For Each Route In Sheets("Data").Range("A2", Selection.End(xlDown))

make a new worksheet
look in the main table for records matching my criteria
insert those records into the new worksheet

Next Route

End For

I'm not sure of the best way to select the records I want from the main
table and append them to the worksheet I just created.

I've done quite a lot of googling for this info but I haven't
foundanything
that fits the bill.

I know I could use nested For... Next but that seems like very inefficient
code. Can I define a Recordset object, use an SQL statement to select the
records I want then append them to the new sheet?

cheers

Damien