Hey guys,
I've actually been here leeching abit here and there for the past week.
But this is where problem started.
Everyday i have spreadsheets generated, which gives me raw data like below which starts from row 6 (heading at row 5)
<table border="0" cellpadding="0" cellspacing="0" width="298"><colgroup><col style="mso-width-source:userset;mso-width-alt:3730;width:77pt" width="102"> <col style="mso-width-source:userset;mso-width-alt:4827;width:99pt" width="132"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:77pt" height="20" width="102">Heading1</td> <td class="xl65" style="width:99pt" width="132">Heading2</td> <td class="xl65" style="width:48pt" width="64">Heading3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">MCD123</td> <td class="xl65">A587</td> <td class="xl65">1.1</td> </tr> <tr style="mso-height-source:userset;height:14.25pt" height="19"> <td class="xl65" style="height:14.25pt" height="19">KFC588</td> <td class="xl65">A235</td> <td class="xl65">1.88</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">MCD128</td> <td class="xl65">A578</td> <td class="xl65">1.3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">ANW887</td> <td class="xl65">A876</td> <td class="xl65">5.58</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">KFC158</td> <td class="xl65">A578</td> <td class="xl65">1.6</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">ANW125</td> <td class="xl65">A215</td> <td class="xl65">1.57</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">KFC782</td> <td class="xl65">A876</td> <td class="xl65">1.75</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">ANW796</td> <td class="xl65">A354</td> <td class="xl65">0.21</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">NAN100</td> <td class="xl65">A578</td> <td class="xl65">5.68</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">MCD786</td> <td class="xl65">A221</td> <td class="xl65">1.5</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">NAN873</td> <td class="xl65">A5990</td> <td class="xl65">7.44</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">MCD555</td> <td class="xl65">A575</td> <td class="xl65">1.25</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">NAN123</td> <td class="xl65">A578</td> <td class="xl65">9.14</td> </tr> </tbody></table>
What i need done is actually have a script extract data from heading1 into different sheets with different headings. (Heading1 would always start with MCD, NAN, KFC, ANW BUT the last 3 numbers are random)
I seen a macro with strings, integer and variable that does it.
And would actually like to learn how to do it. what i did now is made it arrange according to heading1 and then have different commands to extract the data.
I would appreciate if i could learn the commands like While, If, variables and all.
For example,
heading1 = 6
While ("A" & heading1 > 0
*If MCD exists, creates new spreadsheet with MCD as name, Cuts and paste whole line (A6 to C6), then records a counter, best if i could include the header from 5:5 in all the other sheets*
if ("A" & heading1 , begins with MCD) then
Range(sheet1,"A" &heading1 & ":" & "C" &heading1).select
Cut to sheet2
it's even better if i could find the first instance where it starts with MCD, and then last instance where it starts with MCD (this works because it's sorted) and then cut and paste all in one shot.
Continues until no MCD exists. Then renames the spreadsheet to MCD & " - " Counter
then continues to ANW, same thing repeats itself. Just need some guidance for at least MCD, and i think i'll get the flow for the rest.
I really appreciate all the help. =) Anyways, the above data is jz an example, Data actually goes up to 500 rows from column A to Column BB.
So... yeah. =)
Thanks,
Tokarin =)
I've actually been here leeching abit here and there for the past week.
But this is where problem started.
Everyday i have spreadsheets generated, which gives me raw data like below which starts from row 6 (heading at row 5)
<table border="0" cellpadding="0" cellspacing="0" width="298"><colgroup><col style="mso-width-source:userset;mso-width-alt:3730;width:77pt" width="102"> <col style="mso-width-source:userset;mso-width-alt:4827;width:99pt" width="132"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:77pt" height="20" width="102">Heading1</td> <td class="xl65" style="width:99pt" width="132">Heading2</td> <td class="xl65" style="width:48pt" width="64">Heading3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">MCD123</td> <td class="xl65">A587</td> <td class="xl65">1.1</td> </tr> <tr style="mso-height-source:userset;height:14.25pt" height="19"> <td class="xl65" style="height:14.25pt" height="19">KFC588</td> <td class="xl65">A235</td> <td class="xl65">1.88</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">MCD128</td> <td class="xl65">A578</td> <td class="xl65">1.3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">ANW887</td> <td class="xl65">A876</td> <td class="xl65">5.58</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">KFC158</td> <td class="xl65">A578</td> <td class="xl65">1.6</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">ANW125</td> <td class="xl65">A215</td> <td class="xl65">1.57</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">KFC782</td> <td class="xl65">A876</td> <td class="xl65">1.75</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">ANW796</td> <td class="xl65">A354</td> <td class="xl65">0.21</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">NAN100</td> <td class="xl65">A578</td> <td class="xl65">5.68</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">MCD786</td> <td class="xl65">A221</td> <td class="xl65">1.5</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">NAN873</td> <td class="xl65">A5990</td> <td class="xl65">7.44</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">MCD555</td> <td class="xl65">A575</td> <td class="xl65">1.25</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">NAN123</td> <td class="xl65">A578</td> <td class="xl65">9.14</td> </tr> </tbody></table>
What i need done is actually have a script extract data from heading1 into different sheets with different headings. (Heading1 would always start with MCD, NAN, KFC, ANW BUT the last 3 numbers are random)
I seen a macro with strings, integer and variable that does it.
And would actually like to learn how to do it. what i did now is made it arrange according to heading1 and then have different commands to extract the data.
I would appreciate if i could learn the commands like While, If, variables and all.
For example,
heading1 = 6
While ("A" & heading1 > 0
*If MCD exists, creates new spreadsheet with MCD as name, Cuts and paste whole line (A6 to C6), then records a counter, best if i could include the header from 5:5 in all the other sheets*
if ("A" & heading1 , begins with MCD) then
Range(sheet1,"A" &heading1 & ":" & "C" &heading1).select
Cut to sheet2
it's even better if i could find the first instance where it starts with MCD, and then last instance where it starts with MCD (this works because it's sorted) and then cut and paste all in one shot.
Continues until no MCD exists. Then renames the spreadsheet to MCD & " - " Counter
then continues to ANW, same thing repeats itself. Just need some guidance for at least MCD, and i think i'll get the flow for the rest.
I really appreciate all the help. =) Anyways, the above data is jz an example, Data actually goes up to 500 rows from column A to Column BB.
So... yeah. =)
Thanks,
Tokarin =)