Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA question: adding a row to worksheet from recordset row | Excel Programming | |||
Return ADO recordset to worksheet | Excel Programming | |||
Repost! Excel multiple worksheet populate from recordset | Excel Programming | |||
Help! Excel multiple worksheet populate from recordset | Excel Programming | |||
Type recordset/recordset? | Excel Programming |