****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 10">****** name="Originator" content="Microsoft Word 10"><link rel="File-List" href="file:///C:%5CUsers%5CBerries%5CAppData%5CLocal%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:595.3pt 841.9pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:35.4pt; mso-footer-margin:35.4pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman";} </style> <![endif]--> I have a product costing model that extracts materials usage for each of the approximately 600 product codes (will still grow by another 100 or so) from several other worksheets (materials schedules) within the same workbook. The materials schedules are constructed in a standard schedule format, with the relevant product code across the top of the schedule and the list of materials and prices running down the left-hand side. Each schedule is populated with the quantity of each relevant material type in the respective column of each product code.<o
></o>
<o> </o>
There are 4 different types of materials schedules and (due to the grid-size limitations of Excel 2003), up to 3 schedules of each type to cater for the number of product codes. Each materials schedule can have up to 700 different material codes, but generally only a few materials from each schedule are used in any 1 product. Not all products contain materials from each of the schedule types. <o></o>
<o> </o>
We are about to implement new accounting software and require all of this data to be imported. The import requires that the product codes are listed in a single list. Each product code requires a new line in the list for each material type that it consumes, with each new line containing the product code in column A and the material code in column B. I have set up formulae to lookup all other required information once I have this list. I am hoping to be able to automate building the list as to do this manually would involve going through each schedule column by column and filtering out the blanks, then copying and pasting the used materials codes into my new list, and copying the product code down for the number of required rows.<o></o>
<o> </o>
What I need is a macro that will:<o></o>
Work through each of the materials schedules moving across row 1, and for each code, copy into column B of the new list ONLY the materials codes used in that product and into column A the product code. <o></o>
<o> </o>
The new list will be in a separate workbook (which will contain the macro) to the main costing schedules. I would ideally like for there to be a separate list for each schedule type. I will then have the macro join the four lists together to form a single list. The reason for this is that the formulae that I will be using to bring in required data identifies which materials schedule to read from for any given product code through the use of an "=Indirect()" function that concatenates the relevant material category and the schedule number (contained in the index). <o></o>
<o> </o>
<o> </o>
These are the details of the materials schedules:<o></o>
<table class="MsoNormalTable" style="width: 408pt; margin-left: 4.8pt; border-collapse: collapse;" width="544" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 38.25pt;"> <td style="width: 104pt; border: 1pt solid windowtext; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0cm 5.4pt; height: 38.25pt;" valign="bottom" width="139"> Schedule<o></o>
</td> <td style="width: 107pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0cm 5.4pt; height: 38.25pt;" valign="bottom" width="143"> Number of schedules of this type<o></o>
</td> <td style="width: 108pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0cm 5.4pt; height: 38.25pt;" valign="bottom" width="144"> Column containing materials codes<o></o>
</td> <td style="width: 89pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0cm 5.4pt; height: 38.25pt;" valign="bottom" width="119"> Column commencing first product code<o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 104pt; border-right: 1pt solid windowtext; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0cm 5.4pt; height: 12.75pt;" valign="bottom" width="139" nowrap="nowrap"> Material category A<o></o>
</td> <td style="width: 107pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 5.4pt; height: 12.75pt;" valign="bottom" width="143" nowrap="nowrap"> </td> <td style="width: 108pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 5.4pt; height: 12.75pt;" valign="bottom" width="144" nowrap="nowrap"> B<o></o>
</td> <td style="width: 89pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 5.4pt; height: 12.75pt;" valign="bottom" width="119" nowrap="nowrap"> J<o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 104pt; border-right: 1pt solid windowtext; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0cm 5.4pt; height: 12.75pt;" valign="bottom" width="139" nowrap="nowrap"> Material category B<o></o>
</td> <td style="width: 107pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 5.4pt; height: 12.75pt;" valign="bottom" width="143" nowrap="nowrap"> </td> <td style="width: 108pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 5.4pt; height: 12.75pt;" valign="bottom" width="144" nowrap="nowrap"> B<o></o>
</td> <td style="width: 89pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 5.4pt; height: 12.75pt;" valign="bottom" width="119" nowrap="nowrap"> J<o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 104pt; border-right: 1pt solid windowtext; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0cm 5.4pt; height: 12.75pt;" valign="bottom" width="139" nowrap="nowrap"> Material category C<o></o>
</td> <td style="width: 107pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 5.4pt; height: 12.75pt;" valign="bottom" width="143" nowrap="nowrap"> </td> <td style="width: 108pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 5.4pt; height: 12.75pt;" valign="bottom" width="144" nowrap="nowrap"> A<o></o>
</td> <td style="width: 89pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 5.4pt; height: 12.75pt;" valign="bottom" width="119" nowrap="nowrap"> H<o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 104pt; border-right: 1pt solid windowtext; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0cm 5.4pt; height: 12.75pt;" valign="bottom" width="139" nowrap="nowrap"> Material category D<o></o>
</td> <td style="width: 107pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 5.4pt; height: 12.75pt;" valign="bottom" width="143" nowrap="nowrap"> </td> <td style="width: 108pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 5.4pt; height: 12.75pt;" valign="bottom" width="144" nowrap="nowrap"> A<o></o>
</td> <td style="width: 89pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 5.4pt; height: 12.75pt;" valign="bottom" width="119" nowrap="nowrap"> G<o></o>
</td> </tr> </tbody></table> <o> </o>
<o> </o>
A massive thanks in advance for anyone out there who is going to give this a go. I hope that I have managed to make sense of what I am trying to do.
></o><o
> </o>There are 4 different types of materials schedules and (due to the grid-size limitations of Excel 2003), up to 3 schedules of each type to cater for the number of product codes. Each materials schedule can have up to 700 different material codes, but generally only a few materials from each schedule are used in any 1 product. Not all products contain materials from each of the schedule types. <o
></o><o
> </o>We are about to implement new accounting software and require all of this data to be imported. The import requires that the product codes are listed in a single list. Each product code requires a new line in the list for each material type that it consumes, with each new line containing the product code in column A and the material code in column B. I have set up formulae to lookup all other required information once I have this list. I am hoping to be able to automate building the list as to do this manually would involve going through each schedule column by column and filtering out the blanks, then copying and pasting the used materials codes into my new list, and copying the product code down for the number of required rows.<o
></o><o
> </o>What I need is a macro that will:<o
></o>Work through each of the materials schedules moving across row 1, and for each code, copy into column B of the new list ONLY the materials codes used in that product and into column A the product code. <o
></o><o
> </o>The new list will be in a separate workbook (which will contain the macro) to the main costing schedules. I would ideally like for there to be a separate list for each schedule type. I will then have the macro join the four lists together to form a single list. The reason for this is that the formulae that I will be using to bring in required data identifies which materials schedule to read from for any given product code through the use of an "=Indirect()" function that concatenates the relevant material category and the schedule number (contained in the index). <o
></o><o
> </o><o
> </o>These are the details of the materials schedules:<o
></o><table class="MsoNormalTable" style="width: 408pt; margin-left: 4.8pt; border-collapse: collapse;" width="544" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 38.25pt;"> <td style="width: 104pt; border: 1pt solid windowtext; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0cm 5.4pt; height: 38.25pt;" valign="bottom" width="139"> Schedule<o
></o></td> <td style="width: 107pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0cm 5.4pt; height: 38.25pt;" valign="bottom" width="143"> Number of schedules of this type<o
></o></td> <td style="width: 108pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0cm 5.4pt; height: 38.25pt;" valign="bottom" width="144"> Column containing materials codes<o
></o></td> <td style="width: 89pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0cm 5.4pt; height: 38.25pt;" valign="bottom" width="119"> Column commencing first product code<o
></o></td> </tr> <tr style="height: 12.75pt;"> <td style="width: 104pt; border-right: 1pt solid windowtext; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0cm 5.4pt; height: 12.75pt;" valign="bottom" width="139" nowrap="nowrap"> Material category A<o
></o></td> <td style="width: 107pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 5.4pt; height: 12.75pt;" valign="bottom" width="143" nowrap="nowrap">
3<o></o>
></o>></o></td> <td style="width: 89pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 5.4pt; height: 12.75pt;" valign="bottom" width="119" nowrap="nowrap"> J<o
></o></td> </tr> <tr style="height: 12.75pt;"> <td style="width: 104pt; border-right: 1pt solid windowtext; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0cm 5.4pt; height: 12.75pt;" valign="bottom" width="139" nowrap="nowrap"> Material category B<o
></o></td> <td style="width: 107pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 5.4pt; height: 12.75pt;" valign="bottom" width="143" nowrap="nowrap">
3<o></o>
></o>></o></td> <td style="width: 89pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 5.4pt; height: 12.75pt;" valign="bottom" width="119" nowrap="nowrap"> J<o
></o></td> </tr> <tr style="height: 12.75pt;"> <td style="width: 104pt; border-right: 1pt solid windowtext; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0cm 5.4pt; height: 12.75pt;" valign="bottom" width="139" nowrap="nowrap"> Material category C<o
></o></td> <td style="width: 107pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 5.4pt; height: 12.75pt;" valign="bottom" width="143" nowrap="nowrap">
3<o></o>
></o>></o></td> <td style="width: 89pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 5.4pt; height: 12.75pt;" valign="bottom" width="119" nowrap="nowrap"> H<o
></o></td> </tr> <tr style="height: 12.75pt;"> <td style="width: 104pt; border-right: 1pt solid windowtext; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% rgb(204, 255, 204); padding: 0cm 5.4pt; height: 12.75pt;" valign="bottom" width="139" nowrap="nowrap"> Material category D<o
></o></td> <td style="width: 107pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 5.4pt; height: 12.75pt;" valign="bottom" width="143" nowrap="nowrap">
1<o></o>
></o>></o></td> <td style="width: 89pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 5.4pt; height: 12.75pt;" valign="bottom" width="119" nowrap="nowrap"> G<o
></o></td> </tr> </tbody></table> <o
> </o><o
> </o>A massive thanks in advance for anyone out there who is going to give this a go. I hope that I have managed to make sense of what I am trying to do.
