Split one sheet to many workbooks

Posted on 2014-07-14
Last Modified: 2014-07-15
I have a workbook (List_Distribution_DuplicatesRemoved.xls) that has a number of shhets (tabs) and each sheet has around 10,000 rows of data (some more some less).

I need to split each sheet into individual workbooks for every 1,000 rows and then name those files based on the tab name of each of the tab.

So for example there is a sheet in the workbook named Brooks Boucher. On this sheet there is approx 10,000 rows. I need a function that will divide that sheet into 1,000 rows (10 separate workbooks) and then name the new files: Brooks Boucher 1.csv, Brooks Boucher 2.csv, Brooks Boucher 3.csv and so on up until all the rows are taken from the original 10,000 rowed sheet.

If converting it from a xls to cvs is not doable or problematic then just having them as .xls files will be fine and I will rename.

And, I need the above for each tab I have in the List_Distribution_DuplicatesRemoved.xls workbook.

Would save me a LOT of time if someone could help me with this.
Question by:mabehr
    LVL 20

    Accepted Solution

    Try this, see sample file with 2 sheets.
    Insert the code in a module in the workbook List_Distribution_DuplicatesRemoved.xls.
    The csv files are created in the same folder as the workbook, and will be named
    Brooks Boucher 001.CSV, Brooks Boucher 002.CSV etc., or whatever the tab name is.
    The first row (Header) is repeated for each file.

    Option Explicit
    Dim ws As Worksheet, wsCsv As Worksheet
    Dim rwMax As Long, rwStart As Long, rwEnd As Long, colMax As Integer
    Dim FileNbr As Integer, FileNbrMax As Integer
    Dim CsvFileName As String
    Const MaxRowsInFiles As Long = 1000
    Sub MakeCsvFiles()
        ChDrive Left(ThisWorkbook.Path, 2)
        ChDir ThisWorkbook.Path
        Application.ScreenUpdating = False
        For Each ws In ThisWorkbook.Worksheets
            rwMax = ws.Range("A1").CurrentRegion.Rows.Count
            colMax = ws.Range("A1").CurrentRegion.Columns.Count
            FileNbrMax = Int(rwMax / (MaxRowsInFiles - 1)) + 1
            rwStart = 2
            For FileNbr = 1 To FileNbrMax
                CsvFileName = ws.Name + " "
                If FileNbr < 100 Then CsvFileName = CsvFileName + "0"
                If FileNbr < 10 Then CsvFileName = CsvFileName + "0"
                CsvFileName = CsvFileName + Trim(Str(FileNbr))
                rwEnd = rwStart + MaxRowsInFiles - 2
                If rwEnd > rwMax Then
                    rwEnd = rwMax
                End If
                Set wsCsv = Worksheets.Add
                If Len(CsvFileName) > 31 Then
                    wsCsv.Name = Left(CsvFileName, 27) + Right(CsvFileName, 4)
                    wsCsv.Name = CsvFileName
                End If
                ws.Range(Cells(1, 1), Cells(1, colMax)).Copy
                Application.CutCopyMode = False
                ws.Range(Cells(rwStart, 1), Cells(rwEnd, colMax)).Copy
                Application.CutCopyMode = False
                Application.DisplayAlerts = False
                ActiveWorkbook.SaveAs Filename:=CsvFileName, FileFormat:=xlCSV
                Application.DisplayAlerts = True
                ActiveWorkbook.Close Savechanges:=False
                rwStart = rwEnd + 1
            Next FileNbr
        Next ws
    End Sub

    Open in new window


    Author Closing Comment

    Talk about stupendous! Wow! That worked marvelously. Thanks Ejgil. I had twenty sheets on the original workbook and it spit everyone of them into csv files of 1,000 rows each, creating over 200 csv files all named correctly,

    Couldn't ask for anything more.

    Thank you!!!

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

    730 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now