• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

Split an worksheet into multiple files

Hi guys,

I have a worksheet with about 15000 records organized in a table as follows;

Field1   Fiel 2      Field3    Field4    Field5    Field6
Data     Name1  Data      data        data      data
data     Name1   data      data        data      data
Data     Name2 Data      data        data       data
data     Name2   data      data        data      data

Can anyone give the VBA code to split the worksheet in multiple excel files = Number of Names.
For each Name in the Field 2 I would like to have a file. The number of records is different from one name to another. So a name can have 33 records and another name 333 records.       The number of names is about 70.
Thank you very much,
0
marian68
Asked:
marian68
  • 2
  • 2
  • 2
2 Solutions
 
Saqib Husain, SyedEngineerCommented:
Sub split2sheets()
    Dim sws As Worksheet
    Dim tws As Worksheet
    Dim i As Long
    Dim rstrt As Long
    Dim rend As Long
    Set sws = ActiveSheet
    rstrt = 2
    For i = 3 To sws.Range("B" & Rows.Count).End(xlUp).Row + 1
        If sws.Cells(i, 2) <> sws.Cells(i - 1, 2) Then
            Set tws = Worksheets.Add
            tws.Name = sws.Cells(rstrt, 2)
            sws.Range("A1").EntireRow.Copy tws.Range("A1")
            sws.Range(sws.Cells(rstrt, 1), sws.Cells(i - 1, 1)).EntireRow.Copy tws.Range("A2")
            tws.Move
            Application.DisplayAlerts = False
            ActiveWorkbook.SaveAs sws.Parent.Path & "\" & ActiveSheet.Name & ".xls"
            Application.DisplayAlerts = True
            ActiveWorkbook.Close
            rstrt = i
        End If
    Next i
End Sub
0
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

The code provided by ssaqibh in your earlier question, could be changed slightly thus:

Sub split2workbooks()
    
' See also: [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28288113.html#a39632141 ]

  Dim i                                                 As Long
  Dim lngApplication_SheetsInNewWorkbook                As Long
  Dim objWorkbook                                       As Workbook
  Dim rend                                              As Long
  Dim rstrt                                             As Long
  Dim sws                                               As Worksheet
  Dim tws                                               As Worksheet
  
  lngApplication_SheetsInNewWorkbook = Application.SheetsInNewWorkbook
  Application.SheetsInNewWorkbook = 1&
    
  Set sws = ActiveSheet
  
  rstrt = 2
  
  For i = 3 To sws.Range("B" & Rows.Count).End(xlUp).Row + 1
      
      If sws.Cells(i, 2) <> sws.Cells(i - 1, 2) Then
         Set objWorkbook = Workbooks.Add
         Set tws = objWorkbook.Worksheets(1&)
           
         tws.Name = Left$(sws.Cells(rstrt, 2), 31)
         sws.Range("A1").EntireRow.Copy tws.Range("A1")
         sws.Range(sws.Cells(rstrt, 1), sws.Cells(i - 1, 1)).EntireRow.Copy tws.Range("A2")
         
         rstrt = i
           
         Application.DisplayAlerts = False
           
         objWorkbook.Close SaveChanges:=True, _
                           Filename:="C:\" & tws.Name
                             
         Application.DisplayAlerts = True
      End If
      
  Next i
    
  Application.SheetsInNewWorkbook = lngApplication_SheetsInNewWorkbook

  Set objWorkbook = Nothing
  
End Sub

Open in new window



Please change this line to reference the required folder where the individual workbooks should be saved:

         objWorkbook.Close SaveChanges:=True, _
                           Filename:="C:\" & tws.Name

Presently they are saved in the root folder of drive C:

BFN,

fp.
Q-28289006.xls
0
 
[ fanpages ]IT Services ConsultantCommented:
^ Twenty four seconds! :)
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
Saqib Husain, SyedEngineerCommented:
Cost of perfection with the extra comments and instructions.
0
 
marian68Author Commented:
The both solution are excellent. For me the difference in speed is not noticeable.
Anyway the solution of ssaqibh doesn't need a path and suits me better.
Thank you both and have a nice day
0
 
marian68Author Commented:
I don't know if there is a point to ask another question.
It is possible to improve this code so that the data in each created file is organized as a table?
Let me know please if the answer is no or yes to ask another question
Thank you
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now