+ Reply to Thread
Results 1 to 2 of 2

Split single Excel file into multiple files

Hybrid View

  1. #1
    Registered User
    Join Date
    07-20-2012
    Location
    Dallas Texas
    MS-Off Ver
    Excel 2010
    Posts
    1

    Split single Excel file into multiple files

    Hi Everyone,

    I have to admit my experience/knowledge with macros in Excel is very limited.

    Anyway here is what I have and what I would like to try and do via a Macro:

    What I have:
    I have an Excel 2010 file that has 1 Column and approx. 9,000 Rows of Data.

    What I would like to do:
    I'm looking for a macro that will break apart the 9,000 rows into 300 rows per each file.

    Example:

    Orginial.xls contains Row 1-9,000
    Rows 1-300 moved to NewFile1.XLS
    Rows 1-300 deleted from Original.xls
    Rows 1-300 moved to NewFile2.XLS
    Rows 1-300 deleted from Original.xls
    Rows 1-300 moved to NewFile3.xls
    etc...

    After it's all said and done:
    Orginial.xls = empty
    NewFile1.xls - NewFile30.xls created with 300 rows each.

    Hopefully this makes sense

    Thanks for your time.

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Split single Excel file into multiple files

    Sub splitxls()
    lastcell = Range("A1").SpecialCells(xlLastCell).Row
    numrows = 300
    numfile = Int(lastcell / numrows) + 1
    For j = 1 To numfile
        Range("A1:A" & numrows).Cut
        Workbooks.Add
        ActiveSheet.Paste
        Application.CutCopyMode = False
        ActiveWorkbook.SaveAs Filename:= _
        "D:\DATI\prova\" & j & ".xls", FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
        ActiveWorkbook.Close
        Range("A1:A" & numrows).Rows.Delete
    Next
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1