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,

Here is the part of the code that decides which sheet gets what data. I have changed it to include "Warranty Credit" all the time.

                Select Case LCase(Cell)
                    Case Is = "cash credit", "warranty credit"
                        Select Case Depot
                            Case 1, 4, 6, 10: Set Wks = Worksheets("PF Cash Credits")
                            Case 2, 8, 9, 12: Set Wks = Worksheets("LP Cash Credits")
                            Case 3, 5, 7, 11: Set Wks = Worksheets("SC Cash Credits")
                            Case 14: Set Wks = Worksheets("D14 Cash Credits")
                        End Select
 
                    Case Is = "account credit", "warranty credit"
                        Select Case Reason
                            Case 2, 4, 5, 6, 33
                                Select Case Depot
                                    Case 1, 4, 6, 10: Set Wks = Worksheets("PF Account Credits")
                                    Case 2, 8, 9, 12: Set Wks = Worksheets("LP Account Credits")
                                    Case 3, 5, 7, 11: Set Wks = Worksheets("SC Account Credits")
                                    Case 14: Set Wks = Worksheets("D14 Account Credits")
                                End Select
                        End Select
                End Select

 

[Dazzawm]

So any 'warranty credit' will go in the 'cash credit' sheets now? If so that is incorrect I would want them to go in the 'account credit' sheets. Thanks.

Also any thoughts on post 10?

 

[Leith Ross]

Hello Dazwm,

The change will add the "Warranty Credit" to both. The original version (before the change) will add the "Warranty Credit" only to the "Account Credit" sheets.

 

[Dazzawm]

How difficult would this be or would it be totally rewritting the code:-

Rather than creating new sheets in the data file could each 2 sets of sheets put in their on workbook within a folder.

For example the main data file is in a folder on my desktop. Could the code do as it does now but instead of creating the new sheets in that file create new workbooks called PF Credits with the 2 sheets inside, LP Credits with the 2 sheets inside and so on...

 

[Leith Ross]

Hello dazwm,

I can do that for you. Will the workbooks be paced in existing folders? If so, what is the folder path?

 

[Dazzawm]

For now I have created a folder on my desktop which the data file is in and the new workbooks can be created in there. I can change the path to suit once the code has been written. C:\Users\Daz\Desktop\New folder (2)

Thank you.

 

[Leith Ross]

Hello dazwm,

I want to double check on the new workbook names and sheets. The workbook "PF Credits" would have the worksheets "PF Cash Credits" and "PF Account Credits", correct?

 

[Dazzawm]

That is correct and the others in the same format please.

 

[Leith Ross]

Hello dazwm,

Here is the revised macro. It will create a workbook for each credit pair. The information from the "Data" sheet will copied to the correct workbook and worksheet.

If the workbook already exists then any existing data will be overwritten. I can change this if needed. New workbook will be created with 2 worksheets and saved in the folder "New Folder (2)" on the desktop. If folder is not found then a error message is displayed and the macro will stop. The workbooks all remain open but can be closed by the macro if need. Let know what you want to change.

' Written: May 29, 2012
' Author:  Leith Ross (www.mrexcel.com)

Sub SeparateData()

    Dim Cell As Range
    Dim Depot As Long
    Dim Ext As String
    Dim Filename As String
    Dim FolderName As String
    Dim FolderPath As String
    Dim FirstWks As Worksheet
    Dim HeaderRow As Range
    Dim NewSheets(1 To 8) As String
    Dim R As Long
    Dim Reason As Long
    Dim Rng As Range
    Dim Wkb As Workbook
    Dim WkbName As Variant
    Dim Wks As Worksheet
 
        Application.ScreenUpdating = False
 
        Application.SheetsInNewWorkbook = 2
        
        FolderName = "New Folder (2)"

      ' Get the path to user's desktop.
        FolderPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & FolderName
        
            Set FirstWks = Worksheets(1)
 
            For Each Wks In Worksheets
                If StrComp(Wks.CodeName, FirstWks.CodeName, vbTextCompare) = -1 Then
                    Set FirstWks = Wks
                End If
            Next Wks
 
            Set Rng = FirstWks.Range("A1").CurrentRegion
            Set Rng = Intersect(Rng, Rng.Offset(1, 0))
 
            If Rng Is Nothing Then Exit Sub
        
        
            If Dir(FolderPath, vbDirectory) = "" Then
                MsgBox "The Folder '" & FolderPath & "' could not be found.", vbCritical
                Exit Sub
            End If
        
                Set HeaderRow = FirstWks.Range("A1:J1")
            
                If Val(Application.Version) < 12 Then Ext = ".xls" Else Ext = ".xlsx"
            
                For Each WkbName In Array("PF Credits", "LP Credits", "SC Credits", "D14 Credits")
                    Filename = WkbName & Ext
                
                    If Dir(FolderPath & "\" & Filename) <> "" Then
                        Set Wkb = Workbooks.Open(FolderPath & "\" & Filename)
                            Wkb.Worksheets(1).UsedRange.ClearContents
                            Wkb.Worksheets(2).UsedRange.ClearContents
                    Else
                        Set Wkb = Workbooks.Add()
                            WkbName = Split(WkbName, " ")
                            Wkb.Worksheets(1).Name = WkbName(0) & " Cash " & WkbName(1)
                            Wkb.Worksheets(2).Name = WkbName(0) & " Accounts " & WkbName(1)
                        Wkb.SaveAs FolderPath & "\" & WkbName(0) & " " & WkbName(1)
                    End If
                
                    HeaderRow.Copy Wkb.Worksheets(1).Range("A1")
                    HeaderRow.Copy Wkb.Worksheets(2).Range("A1")

                Next WkbName
        
 
            For Each Cell In Rng.Columns(3).Cells
                Reason = Cell.Offset(0, 2)
                Depot = Cell.Offset(0, 3)
                Set Wks = Nothing
 
                Select Case LCase(Cell)
                    Case Is = "cash credit"
                        Select Case Depot
                            Case 1, 4, 6, 10: Set Wks = Workbooks("PF Credits" & Ext).Worksheets("PF Cash Credits")
                            Case 2, 8, 9, 12: Set Wks = Workbooks("LP Credits" & Ext).Worksheets("LP Cash Credits")
                            Case 3, 5, 7, 11: Set Wks = Workbooks("SC Credits" & Ext).Worksheets("SC Cash Credits")
                            Case 14: Set Wks = Workbooks("D14 Credits" & Ext).Worksheets("D14 Cash Credits")
                        End Select
 
                    Case Is = "account credit", "warranty credit"
                        Select Case Reason
                            Case 2, 4, 5, 6, 33
                                Select Case Depot
                                    Case 1, 4, 6, 10: Set Wks = Workbooks("PF Credits" & Ext).Worksheets("PF Account Credits")
                                    Case 2, 8, 9, 12: Set Wks = Workbooks("LP Credits" & Ext).Worksheets("LP Account Credits")
                                    Case 3, 5, 7, 11: Set Wks = Workbooks("SC Credits" & Ext).Worksheets("SC Account Credits")
                                    Case 14: Set Wks = Workbooks("D14 Credits" & Ext).Worksheets("D14 Account Credits")
                                End Select
                        End Select
                End Select
 
                If Not Wks Is Nothing Then
                    R = Wks.Cells(Rows.Count, "A").End(xlUp).Row + 1
                    Cell.EntireRow.Copy Destination:=Wks.Cells(R, "A")
                    Wks.Columns.AutoFit
                    Wks.Rows.AutoFit
                   Rng.Cells.Sort Key1:=Sheet1.Cells(2, 5), Order1:=xlAscending, Header:=xlYes
                End If
            Next Cell
            
            For Each Wkb In Workbooks
                Wkb.Save
            Next Wkb
            
   Sheets(1).Activate
   Cells.EntireColumn.AutoFit
   Range("A1").Select
   
   Application.ScreenUpdating = True
   Application.SheetsInNewWorkbook = 3
   
End Sub

 

[Dazzawm]

Thanks I get a couple of errors firstly the sort again

Rng.Cells.Sort Key1:=Sheet1.Cells(2, 5), Order1:=xlAscending, Header:=xlYes

and if a put an apostrophe in front and run again I then get an error on

: Set Wks = Workbooks("LP Credits" & Ext).Worksheets("LP Account Credits")