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 Daz,

I understand what your saying. Let me see what I can do about the adjusting the page width.

 

[Leith Ross]

Hello Daz,

This should work for about any situation. The paper size is set to U.S. legal (8.5 in. x 14 in.) You may need to change this to match the paper size you are using. It must be larger than 8.5 in. x 11 in. to display properly in landscape.

    For Each Wks In Worksheets
        If Wks.Name Like "*Credits" Then
            With Wks.PageSetup
                .Orientation = xlLandscape
                .PaperSize = xlPaperLegal
                .CenterHorizontally = True
                .LeftMargin = Application.InchesToPoints(0.25)
                .RightMargin = Application.InchesToPoints(0.25)
            End With
            Wks.PrintOut
        End If
    Next Wks

 

[Dazzawm]

Our paper size is A4 (8.3in x 11.7in) but looking at the print preview the dotted lines are right over next to column N so all seems fine. I have taken the auto print line out because I don't need that. Thanks for everything.

 

[Leith Ross]

Hello Daz,

Excellent, glad the paper size works as is. The closest ISO sizes to US legal are A4 long and Super A4. You're welcome, it was a interesting project.