Creating New Sheets And Moving Data

[Dazzawm]

I have a file with one sheet. What I need the code is to create 8 new sheets and copy the data across meeting the criteria below.

1. The code needs to look at column C and any rows that say cash credit needs putting onto new sheets in the way below.

2. It then needs to look at column F for the depot. If the number is 1, 4, 6 and 10 then a new sheet needs to be PF Cash. If the number is 2, 8, 9 and 12 there needs to be another sheet called LP Cash. If the numbers are 3, 5, 7 and 11 another sheet called SC Cash, and finally number 14 another sheet called DF Cash

3. What should be left on the main data sheet should be rows called Account Credit and Warranty Credit in C and then more sheets added the same way as above. PF Account, LP Account, SC Account and DF Account. But then it also needs to look at column E and only copy across the rows that have numbers 2, 4, 5, 6 and 33

4. The file will then have 9 sheets in total (the 8 created as above plus the data sheet).

<b>Data</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:MS Sans Serif,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:81px;" /><col style="width:80px;" /><col style="width:133px;" /><col style="width:68px;" /><col style="width:62px;" /><col style="width:51px;" /><col style="width:69px;" /><col style="width:119px;" /><col style="width:70px;" /><col style="width:259px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#c0c0c0; font-family:Arial; text-align:center; ">REGNUM</td><td style="background-color:#c0c0c0; font-family:Arial; text-align:center; ">CUSTOMER</td><td style="background-color:#c0c0c0; font-family:Arial; text-align:center; ">TTYPEST</td><td style="background-color:#c0c0c0; font-family:Arial; text-align:center; ">INVDATE</td><td style="background-color:#c0c0c0; font-family:Arial; text-align:center; ">REASON</td><td style="background-color:#c0c0c0; font-family:Arial; text-align:center; ">DEPOT</td><td style="background-color:#c0c0c0; font-family:Arial; text-align:center; ">INVNUM</td><td style="background-color:#c0c0c0; font-family:Arial; text-align:center; ">STCODE</td><td style="background-color:#c0c0c0; font-family:Arial; text-align:center; ">QUANTITY</td><td style="background-color:#c0c0c0; font-family:Arial; text-align:center; ">DESCRIPN</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-family:Arial; text-align:left; ">CBD</td><td style="font-family:Arial; text-align:left; ">XRE01</td><td style="font-family:Arial; text-align:left; ">CASH CREDIT</td><td style="font-family:Arial; text-align:right; ">21-May-12</td><td style="font-family:Arial; text-align:left; ">07</td><td style="font-family:Arial; text-align:right; ">1</td><td style="font-family:Arial; text-align:left; ">MR123456</td><td style="font-family:Arial; text-align:left; ">ABC123</td><td style="font-family:Arial; text-align:right; ">-2</td><td style="font-family:Arial; text-align:left; ">TEST 1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-family:Arial; text-align:left; ">R0634930</td><td style="font-family:Arial; text-align:left; ">XRE06</td><td style="font-family:Arial; text-align:left; ">CASH CREDIT</td><td style="font-family:Arial; text-align:right; ">21-May-12</td><td style="font-family:Arial; text-align:left; ">03</td><td style="font-family:Arial; text-align:right; ">6</td><td style="font-family:Arial; text-align:left; ">MR123457</td><td style="font-family:Arial; text-align:left; ">ABC124</td><td style="font-family:Arial; text-align:right; ">-1</td><td style="font-family:Arial; text-align:left; ">TEST 2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-family:Arial; text-align:left; ">R0157821</td><td style="font-family:Arial; text-align:left; ">XRE11</td><td style="font-family:Arial; text-align:left; ">CASH CREDIT</td><td style="font-family:Arial; text-align:right; ">21-May-12</td><td style="font-family:Arial; text-align:left; ">01</td><td style="font-family:Arial; text-align:right; ">1</td><td style="font-family:Arial; text-align:left; ">MR123458</td><td style="font-family:Arial; text-align:left; ">ABC125</td><td style="font-family:Arial; text-align:right; ">-1</td><td style="font-family:Arial; text-align:left; ">TEST 3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-family:Arial; text-align:left; ">CBD</td><td style="font-family:Arial; text-align:left; ">XRE16</td><td style="font-family:Arial; text-align:left; ">CASH CREDIT</td><td style="font-family:Arial; text-align:right; ">21-May-12</td><td style="font-family:Arial; text-align:left; ">07</td><td style="font-family:Arial; text-align:right; ">4</td><td style="font-family:Arial; text-align:left; ">MR123459</td><td style="font-family:Arial; text-align:left; ">ABC126</td><td style="font-family:Arial; text-align:right; ">-1</td><td style="font-family:Arial; text-align:left; ">TEST 4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-family:Arial; text-align:left; ">R0345826</td><td style="font-family:Arial; text-align:left; ">XRE21</td><td style="font-family:Arial; text-align:left; ">CASH CREDIT</td><td style="font-family:Arial; text-align:right; ">21-May-12</td><td style="font-family:Arial; text-align:left; ">01</td><td style="font-family:Arial; text-align:right; ">3</td><td style="font-family:Arial; text-align:left; ">MR123460</td><td style="font-family:Arial; text-align:left; ">ABC127</td><td style="font-family:Arial; text-align:right; ">-1</td><td style="font-family:Arial; text-align:left; ">TEST 5</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-family:Arial; text-align:left; ">R0345826</td><td style="font-family:Arial; text-align:left; ">XRE26</td><td style="font-family:Arial; text-align:left; ">CASH CREDIT</td><td style="font-family:Arial; text-align:right; ">21-May-12</td><td style="font-family:Arial; text-align:left; ">01</td><td style="font-family:Arial; text-align:right; ">3</td><td style="font-family:Arial; text-align:left; ">MR123461</td><td style="font-family:Arial; text-align:left; ">ABC128</td><td style="font-family:Arial; text-align:right; ">-1</td><td style="font-family:Arial; text-align:left; ">TEST 6</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-family:Arial; text-align:left; ">R0157730</td><td style="font-family:Arial; text-align:left; ">XRE31</td><td style="font-family:Arial; text-align:left; ">CASH CREDIT</td><td style="font-family:Arial; text-align:right; ">21-May-12</td><td style="font-family:Arial; text-align:left; ">03</td><td style="font-family:Arial; text-align:right; ">1</td><td style="font-family:Arial; text-align:left; ">MR123462</td><td style="font-family:Arial; text-align:left; ">ABC129</td><td style="font-family:Arial; text-align:right; ">-1</td><td style="font-family:Arial; text-align:left; ">TEST 7</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-family:Arial; text-align:left; ">CBD</td><td style="font-family:Arial; text-align:left; ">XRE36</td><td style="font-family:Arial; text-align:left; ">CASH CREDIT</td><td style="font-family:Arial; text-align:right; ">21-May-12</td><td style="font-family:Arial; text-align:left; ">07</td><td style="font-family:Arial; text-align:right; ">6</td><td style="font-family:Arial; text-align:left; ">MR123463</td><td style="font-family:Arial; text-align:left; ">ABC130</td><td style="font-family:Arial; text-align:right; ">-1</td><td style="font-family:Arial; text-align:left; ">TEST 8</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-family:Arial; text-align:left; ">R1215272</td><td style="font-family:Arial; text-align:left; ">XRE41</td><td style="font-family:Arial; text-align:left; ">CASH CREDIT</td><td style="font-family:Arial; text-align:right; ">21-May-12</td><td style="font-family:Arial; text-align:left; ">01</td><td style="font-family:Arial; text-align:right; ">12</td><td style="font-family:Arial; text-align:left; ">MR123464</td><td style="font-family:Arial; text-align:left; ">ABC131</td><td style="font-family:Arial; text-align:right; ">-1</td><td style="font-family:Arial; text-align:left; ">TEST 9</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-family:Arial; text-align:left; ">R1215272</td><td style="font-family:Arial; text-align:left; ">XRE46</td><td style="font-family:Arial; text-align:left; ">CASH CREDIT</td><td style="font-family:Arial; text-align:right; ">21-May-12</td><td style="font-family:Arial; text-align:left; ">01</td><td style="font-family:Arial; text-align:right; ">12</td><td style="font-family:Arial; text-align:left; ">MR123465</td><td style="font-family:Arial; text-align:left; ">ABC132</td><td style="font-family:Arial; text-align:right; ">-1</td><td style="font-family:Arial; text-align:left; ">TEST 10</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-family:Arial; text-align:left; ">R0157820</td><td style="font-family:Arial; text-align:left; ">XRE51</td><td style="font-family:Arial; text-align:left; ">CASH CREDIT</td><td style="font-family:Arial; text-align:right; ">21-May-12</td><td style="font-family:Arial; text-align:left; ">01</td><td style="font-family:Arial; text-align:right; ">1</td><td style="font-family:Arial; text-align:left; ">MR123466</td><td style="font-family:Arial; text-align:left; ">ABC133</td><td style="font-family:Arial; text-align:right; ">-1</td><td style="font-family:Arial; text-align:left; ">TEST 11</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-family:Arial; text-align:left; ">R0742616</td><td style="font-family:Arial; text-align:left; ">XRE56</td><td style="font-family:Arial; text-align:left; ">ACCOUNT CREDIT</td><td style="font-family:Arial; text-align:right; ">21-May-12</td><td style="font-family:Arial; text-align:left; ">01</td><td style="font-family:Arial; text-align:right; ">7</td><td style="font-family:Arial; text-align:left; ">MR123467</td><td style="font-family:Arial; text-align:left; ">ABC134</td><td style="font-family:Arial; text-align:right; ">-1</td><td style="font-family:Arial; text-align:left; ">TEST 12</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-family:Arial; text-align:left; ">R0742853</td><td style="font-family:Arial; text-align:left; ">XRE61</td><td style="font-family:Arial; text-align:left; ">ACCOUNT CREDIT</td><td style="font-family:Arial; text-align:right; ">21-May-12</td><td style="font-family:Arial; text-align:left; ">01</td><td style="font-family:Arial; text-align:right; ">7</td><td style="font-family:Arial; text-align:left; ">MR123468</td><td style="font-family:Arial; text-align:left; ">ABC135</td><td style="font-family:Arial; text-align:right; ">-2</td><td style="font-family:Arial; text-align:left; ">TEST 13</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-family:Arial; text-align:left; ">R1418142</td><td style="font-family:Arial; text-align:left; ">XRE66</td><td style="font-family:Arial; text-align:left; ">ACCOUNT CREDIT</td><td style="font-family:Arial; text-align:right; ">21-May-12</td><td style="font-family:Arial; text-align:left; ">01</td><td style="font-family:Arial; text-align:right; ">14</td><td style="font-family:Arial; text-align:left; ">MR123469</td><td style="font-family:Arial; text-align:left; ">ABC136</td><td style="font-family:Arial; text-align:right; ">-1</td><td style="font-family:Arial; text-align:left; ">TEST 14</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-family:Arial; text-align:left; ">R0742616</td><td style="font-family:Arial; text-align:left; ">XRE71</td><td style="font-family:Arial; text-align:left; ">WARRANTY CREDIT</td><td style="font-family:Arial; text-align:right; ">21-May-12</td><td style="font-family:Arial; text-align:left; ">01</td><td style="font-family:Arial; text-align:right; ">7</td><td style="font-family:Arial; text-align:left; ">MR123470</td><td style="font-family:Arial; text-align:left; ">ABC137</td><td style="font-family:Arial; text-align:right; ">-1</td><td style="font-family:Arial; text-align:left; ">TEST 15</td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000;

 

[Leith Ross]

Hello dazwm,

The Sort procedure is different in Excel 2007 and 2010, I'll need to correct that piece of code. However, I do not understand why you receive an error for setting the workbook object variable. What error number is generated?

 

[Dazzawm]

Runtine error 9, subscript out of range

 

[Leith Ross]

Hello dazwm,

That means the workbooks were not created in the folder. Did you check the desktop folder "New Folder (2)" for any workbooks?

The code ran fine on my end. If there were no workbooks they were created. If they existed, existing information was erased before the new data was copied to the sheets.

 

[Dazzawm]

It did create all 4 files and put a little info into each but then stopped at that error.

 

[Leith Ross]

Hello dazwm,

Are the extensions correct? I have the set macro for either "xls" or "xlsx" depending on the version of Excel.

 

[Dazzawm]

The version I am using is 2010, but why would it create all the files and put some info in each then stop?

 

[Leith Ross]

Hello dazwm.

I am not really sure. If the file extensions don't match that could cause a subscript out of range error. Where they "xlsx" ?

 

[Dazzawm]

Yes they are. Would it help if I sent you the complete data file I am using?

 

[Leith Ross]

Hello dazwm,

It couldn't hurt. You can email it to me at LeithRoss...gmail.com

Replace the ... with an @ in the email address.

 

[Dazzawm]

File sent, thanks.