#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    53
    Rep Power
    12

    Cutting up an Excel file


    Hi,
    At a very high level, I would like to break data on an Excel sheet into individual files.
    Data comes in annually, but I would like to separate it into monthly files.
    The format is always the same, right down to the cell.
    Workbooks are named “XT1001” through “XT2050”, and also “RT4500” through “RT4599”
    The data are on the “dataFile” sheet in all cases.

    Basically, I would like:
    rows 1 to 301 to be its own file, and the name of the file should be what’s in cell B1.
    rows 303 to 604 to be its own file, and the name of the file should be what’s in cell B303.
    rows 606 to 907 to be its own file, and the name of the file should be what’s in cell B606.
    rows 909 to 1210 to be its own file, and the name of the file should be what’s in cell B909.
    rows 1212 to 1513 to be its own file, and the name of the file should be what’s in cell B1212.
    rows 1515 to 1816 to be its own file, and the name of the file should be what’s in cell B1515.
    rows 1818 to 2119 to be its own file, and the name of the file should be what’s in cell B1818.
    rows 2121 to 2422 to be its own file, and the name of the file should be what’s in cell B2121.
    rows 2424 to 2725 to be its own file, and the name of the file should be what’s in cell B2424.
    rows 2727 to 3028 to be its own file, and the name of the file should be what’s in cell B2727.
    rows 3030 to 3331 to be its own file, and the name of the file should be what’s in cell B3030.
    rows 3333 to 3634 to be its own file, and the name of the file should be what’s in cell B3333.

    There are some formulae, but I would only like the values.


    If anyone could provide some guidance, it would be so greatly appreciated. The thought of doing this manually is making me sick. But it needs to be done.
    Thank you to anyone who may be able to help,
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    1
    Rep Power
    0
    Well if some of the files have 10 000 rows, and you have 50 excel files, then it is a good bet that you will exceed Excel's 65 536 row limit.

    With that in mind, you should consider importing the data into a Access Database table.
    Last edited by medialint; February 20th, 2012 at 02:12 PM. Reason: removed fake sig link

IMN logo majestic logo threadwatch logo seochat tools logo