Recordset from excel worksheet
 

I have a table of data in an excel worksheet which I need to use to
populate a number of other worksheets with data.
The main table is a list of subcontractor job data. I want to create a
worksheet for each subcontractor then write records from the main table to
the worksheet applicable to each contractor.
I know how to create the worksheets and move them to new workbooks etc, but
I'd appreciate some guidance in coding something that will analyse each
record in the main table then write selected fields from that record into
the appropriate subcontractor worksheet.
Thanks in advance.
Damien

Recordset from excel worksheet
 

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
"Damien McBain" wrote in message
...
I have a table of data in an excel worksheet which I need to use to
populate a number of other worksheets with data.
The main table is a list of subcontractor job data. I want to create a
worksheet for each subcontractor then write records from the main table to
the worksheet applicable to each contractor.
I know how to create the worksheets and move them to new workbooks etc,
but
I'd appreciate some guidance in coding something that will analyse each
record in the main table then write selected fields from that record into
the appropriate subcontractor worksheet.
Thanks in advance.
Damien

Recordset from excel worksheet
 

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

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

Recordset from excel worksheet
 

Otto Moehrbach wrote:

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

Thanks for your suggestions Otto, I'll give that a whirl.