Split an worksheet into multiple files

Posted on 2013-11-08
Medium Priority
Last Modified: 2013-11-08
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,
Question by:marian68
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
LVL 43

Accepted Solution

Saqib Husain, Syed earned 1200 total points
ID: 39633413
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")
            Application.DisplayAlerts = False
            ActiveWorkbook.SaveAs sws.Parent.Path & "\" & ActiveSheet.Name & ".xls"
            Application.DisplayAlerts = True
            rstrt = i
        End If
    Next i
End Sub
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 800 total points
ID: 39633415

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:


LVL 35

Expert Comment

by:[ fanpages ]
ID: 39633417
^ Twenty four seconds! :)

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39633460
Cost of perfection with the extra comments and instructions.

Author Closing Comment

ID: 39633668
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

Author Comment

ID: 39633693
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question