Creating New Sheets And Moving Data

Dazzawm

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,783
Office Version
  1. 365
Platform
  1. Windows
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;
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
When I said 'The code needs to look at column C and any rows that say cash credit needs putting onto new sheets in the way below' I meant below in number 2 not the excel sample!
 
Upvote 0
Hello dazwm,

This macro should be a good starting point. Add a new module to your workbook and paste the code below into. Be sure to enable your trust center to allow macros to run. Let me know if this close to what you want to do.
Code: 
Sub SeparateData()

    Dim Cell As Range
    Dim Depot As Long
    Dim HeaderRow As Range
    Dim NewSheets(1 To 8) As String
    Dim Reason As Long
    Dim Rng As Range
    Dim Wks As Worksheet
    
        Set Rng = Sheet1.Range("A1").CurrentRegion
        Set Rng = Intersect(Rng, Rng.Offset(1, 0))
        
        If Rng Is Nothing Then Exit Sub
        
        Set HeaderRow = Sheet1.Range("A1:J1")
        
        For Each NewSheet In Array("PF Cash", "LP Cash", "SC Cash", "DF Cash", "PF Account", "LP Account", "SC Account", "DF Account")
            On Error Resume Next
                Set Wks = Worksheets(NewSheet)
                If Err = 9 Then
                    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = NewSheet
                    HeaderRow.Copy Destination:=Range("A1")
                    Columns("A:J").AutoFit
                    Range("A2").Select
                End If
            On Error GoTo 0
        Next NewSheet
            
            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 = Worksheets("PF Cash")
                            Case 2, 8, 9, 12: Set Wks = Worksheets("LP Cash")
                            Case 3, 5, 7, 11: Set Wks = Worksheets("SC Cash")
                            Case 14: Set Wks = Worksheets("DF Cash")
                        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")
                                    Case 2, 8, 9, 12: Set Wks = Worksheets("LP Account")
                                    Case 3, 5, 7, 11: Set Wks = Worksheets("SC Account")
                                    Case 14: Set Wks = Worksheets("DF Account")
                                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")
                End If
            Next Cell
            
End Sub
 
Upvote 0
Thats great thanks Leith. Could a couple of small tweaks be done please.

1. Copy the headers in row onto each sheet
2. Then sort by column E each sheet.
3. Then do a Cells.EntireColumn.AutoFit on each sheet.

Also the data sheet may have different names and it would need to be put in a Personal Macro Workbook so it could be used on different files.

Thanks again.
 
Upvote 0
Hello dazwm,

This macro has the changes you requested. The first worksheet created in the workbook has to be the "Data" sheet. The sheet name can be different from "Data" but it must the first created sheet in the workbook based on the worksheet's CodeName property. This property can only be read and can not be changed by the user. This is the only reliable way to be sure it is the first sheet created regardless of name or position.

The "Data" sheet is sorted using column "E" in ascending order. Each new sheet will have the same headers in row 1 as on the "Data" sheet's row 1. Delete the old macro, copy the one below, and paste it into the same module as the old macro.
Code: 
Sub SeparateData()

    Dim Cell As Range
    Dim Depot As Long
    Dim FirstWks As Worksheet
    Dim HeaderRow As Range
    Dim NewSheets(1 To 8) As String
    Dim Reason As Long
    Dim Rng As Range
    Dim Wks As Worksheet
    
        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
        
        FirstWks.Columns.AutoFit
        Rng.Cells.Sort Key1:=Sheet1.Cells(2, 5), Order1:=xlAscending, Header:=xlYes

        Set HeaderRow = FirstWks.Range("A1:J1")
        
        For Each NewSheet In Array("PF Cash", "LP Cash", "SC Cash", "DF Cash", "PF Account", "LP Account", "SC Account", "DF Account")
            On Error Resume Next
                Set Wks = Worksheets(NewSheet)
                If Err = 9 Then
                    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = NewSheet
                    HeaderRow.Copy Destination:=Range("A1")
                    Range("A2").Select
                End If
            On Error GoTo 0
        Next NewSheet
            
            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 = Worksheets("PF Cash")
                            Case 2, 8, 9, 12: Set Wks = Worksheets("LP Cash")
                            Case 3, 5, 7, 11: Set Wks = Worksheets("SC Cash")
                            Case 14: Set Wks = Worksheets("DF Cash")
                        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")
                                    Case 2, 8, 9, 12: Set Wks = Worksheets("LP Account")
                                    Case 3, 5, 7, 11: Set Wks = Worksheets("SC Account")
                                    Case 14: Set Wks = Worksheets("DF Account")
                                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
                End If
            Next Cell
                    
End Sub
 
Upvote 0
It gets an error on this line:-

Code: 
Rng.Cells.Sort Key1:=Sheet1.Cells(2, 5), Order1:=xlAscending, Header:=xlYes
 
Upvote 0
I have cobbled some code together using the recorder and moved the sort problem in post 6 I was getting lower down the code. I have highlighted in red everything I have added, maybe you could have a look to see what I am trying to do by the added code and see if it is ok and efficient. I also changed the names of the created sheets but I know that doesn't make a difference to the code.

Rich (BB code): 
Sub SeparateData()
Application.ScreenUpdating = False
    Dim Cell As Range
    Dim Depot As Long
    Dim FirstWks As Worksheet
    Dim HeaderRow As Range
    Dim NewSheets(1 To 8) As String
    Dim Reason As Long
    Dim Rng As Range
    Dim Wks As Worksheet
 
        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
        Put an apostrophe before these 2 lines below  so they were  not in the code
       ' FirstWks.Columns.AutoFit 
        ' Rng.Cells.Sort Key1:=Sheet1.Cells(2, 5), Order1:=xlAscending, Header:=xlYes
        Set HeaderRow = FirstWks.Range("A1:J1")
 
        For Each NewSheet In Array("PF Cash Credits", "LP Cash Credits", "SC Cash Credits", "D14 Cash Credits", "PF Account Credits", "LP Account Credits", "SC Account Credits", "D14 Account Credits")
            On Error Resume Next
                Set Wks = Worksheets(NewSheet)
                If Err = 9 Then
                    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = NewSheet
                    HeaderRow.Copy Destination:=Range("A1")
                    Range("A2").Select
                End If
            On Error GoTo 0
        Next NewSheet
 
            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 = 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
 
                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
            Sheets("PF Account Credits").Select
   Sheets("PF Account Credits").Move Before:=Sheets(3)
   Sheets("LP Account Credits").Select
   Sheets("LP Account Credits").Move Before:=Sheets(5)
   Sheets("SC Account Credits").Select
   Sheets("SC Account Credits").Move Before:=Sheets(7)
                   ActiveWindow.TabRatio = 0.936
   ActiveWindow.ScrollWorkbookTabs Sheets:=-7
   Sheets(1).Select
   Cells.Select
   Cells.EntireColumn.AutoFit
   Range("A1").Select
   Application.ScreenUpdating = True
End Sub

Thanks for your time Leith
 
Upvote 0
Hello dazwm,

Rather than move the sheets, you can control the order of creation. The statement below will create and name the sheets. Place the names in the order you want the sheets to appear.
Code: 
For Each NewSheet In Array("PF Cash Credits", "LP Cash Credits", "SC Cash Credits", "D14 Cash Credits", "PF Account Credits", "LP Account Credits", "SC Account Credits", "D14 Account Credits")

Remove all the code to move the sheets after you make the change above. You can then activate the first sheet and adjust the columns using the code below.
Code: 
    Sheets(1).Activate
    Cells.EntireColumn.AutoFit
    Range("A1").Select
    Application.ScreenUpdating = True
 
Upvote 0
Also its my fault but I may need all 'warranty credits' adding to the newly created account credit sheets no matter what the numbers are in E. Is there a line of code that could be added that an apostrophe can be put in front so until I know for sure.
 
Upvote 0
Also I have found one slight error on one of the sheets on the sort. The very first row of data on the main data sheet when it gets put on its respective PF Accounts sheet it stays in row 2 like where it is on the data sheet and doesnt get sorted. All the other sheets sort fine.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
537
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
482
mumps
mumps
E
  • Question Question
Adding a custom message.
Replies
1
Views
632
Micron
M
D
  • Question Question
Looking for a way to allocate shifts between teams, maybe a LET formula
Replies
0
Views
79
Dazzybeeguy
D
A
  • Locked
  • Question Question
values across tables found by name
Replies
2
Views
480
Fluff
Fluff

Forum statistics

Threads
1,223,214
Messages
6,170,774
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top