How to divide a MS Excel Spreadsheet in to multiple files

Hello - I was supposed to set up a business / workflow process and need some advice on what system to use to accomplish this project.

Basically, we are supposed to receive a monthly report from a third party in the form of a MS Excel file, either .xls or xlsx.  The workbook should include a single tab with about 10,000 records (about 10 columns), the majority of the columns will include dollar amounts.


Column 1 = Name
Column 2 = Policy Number
Column 3 = Claim Number
Column 4 =  Date
Column 5 = $Premium Amount$
Column 6 = $Claim Amount
Column 7 = $Loss Amount$
Column 8 = $Loss Reserve$
Column 9 = $Adjustment Expense$
Column 10 = $Combined Loss and Expense$

I need to take the spreadsheet and divide it into several individual Excel or PDF files with data grouped by one of the columns (Ex, Column 1, Policy Number)

For instance, say there are 10,000 records but 9,000 unique Policy Numbers, I need to generate 9,000 individual Excel or PDF files including grouped data from the spreadsheet and dump each file into a shared directory.  So each Policy Number would have their own individual file.  We would need to be able to control the way the file name is created/formatted.

We already have a polling type application that will check that shared folder and import the Excel or PDF's into our Document Management program for end user access.

I really don't have any software development experience so I was going try to rely on my experience with either MS Excel or (limited) SQL.  I think I might be able to create a macro (vb Script) within excel or ms access but I wasn't sure either program could handle the creation of 9,000 files

Any thoughts or ideas would be greatly appreciated.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

looking at your question can I assume that the file you intend to create from the unique Policy has only 1 row ?

if yes then why to create separate files ?
Glenn RayExcel VBA DeveloperCommented:
The following code (also included in the attached example file) should be able to split your policy data into individual workbooks for each policy.  
Option Explicit
Global boolGotFile As Boolean
Sub Split_To_Multiple_XL()
    Dim intLR As Long
    Dim c As Integer
    Dim rng As Range
    Dim cl As Object
    Dim strNewSh, strFilePath, strFileName As String
    If boolGotFile Then
        strFilePath = frmSaveFilepath.txtPath
        Exit Sub
    End If
    MsgBox "This process will take several minutes. Click OK to continue.", _
        vbExclamation + vbOKOnly, "Create Policy Files"
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    intLR = Cells.SpecialCells(xlLastCell).Row
    ActiveWorkbook.Worksheets("Master").Sort.SortFields.Add Key:=Range("B2:B" & intLR) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Master").Sort
        .SetRange Range("A1:J" & intLR)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
    End With
    Set rng = Range("B2:B" & intLR)
    For Each cl In rng
        If cl.Value <> cl.Offset(1, 0).Value Then 'create sheet
            strFileName = cl.Value & ".xlsx"
            strNewSh = cl.Value
            Sheets.Add After:=Sheets("Master")
            ActiveSheet.Name = strNewSh
            Range("A" & cl.Row - c & ":J" & cl.Row).Copy
            Application.CutCopyMode = False
            ActiveWorkbook.SaveAs Filename:=strFilePath & strFileName, _
                FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
            c = 0
            c = c + 1
        End If
    Next cl
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    MsgBox "Policy files saved."
End Sub

Open in new window

It assumes that you have a master worksheet (I've named it "Master" here, but you could change the code to suit your actual source sheet name) with data from columns A through J and no blank values in column B (policy numbers).  It also assumes that the policy "numbers" are alphanumeric and don't include characters that might not be allowed for file or sheet names (ex., dashes and periods are okay; asterisks, question marks, slashes are not).

You'll be prompted for a location to store your files; you can create a new directory in this process if you wish.

The macro will sort the existing data by policy number and then proceed.  If only one occurrence (row) of a policy number exists, the new sheet will only have that one row (plus header row).  If more than one occurrence exists, all rows will be shown on the new sheet.

I tested this with 53 files being created from 58 source rows and it took about 12 seconds (Windows 7 Enterprise 64-bit, Excel 2010 32-bit, 2.5GHz quad processor).  You can expect this to take several minutes to run with 10000 rows/9000 possible files.

I HIGHLY RECOMMEND that you save a copy of your original data and that you run this on a sample of it (say, 1000 rows) and store in a blank directory - local (your C: drive) to speed up processing.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Glenn RayExcel VBA DeveloperCommented:

Did you have a chance to review and or test my solution?  If so, and it will work for you, can you please properly close this question by clicking the "Accept this solution" link above my submission above that answers your question?.  This will help ensure that future searches are meaningful to other EE members.

Otherwise, let us know if you have any other issues.

LenCepedaAuthor Commented:
Thank you so much.  Yes, the script works very well.  Ultimately we ran into trouble converting such a large volume of excel files to individual pdf files.  Acrobat and PDF Creator are a bit slow when is comes to volume conversion,

We are still investigating this process and others.  Thank syou
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.