+ Reply to Thread
Results 1 to 3 of 3

Help: Splitting large workbook into separate workbooks based on R column value.

  1. #1
    Registered User
    Join Date
    11-13-2012
    Location
    ann arbor
    MS-Off Ver
    Excel 2010
    Posts
    67

    Help: Splitting large workbook into separate workbooks based on R column value.

    Hi.

    I have 10 large workbooks of about a million rows each. Each of these workbooks have a column R which has a value of 1 to 83. I need to be able to separate each row depending on what value appears in column R in the given row.

    Is this possible?

    EDIT:

    Up until this point I have just been using the filter and copy and pasting but manually doing this will take far to long. I would need to do the same over 830 times and at the point the human error element concerns me as much as the time I would spend on it.

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Help: Splitting large workbook into separate workbooks based on R column value.

    Do you want the macro to split the data into individual sheets or individual workbooks?

    Do you want the macro to pick up each workbook (1 to 10) and perform the actions? Or will you run the macro individually on the 10 workbooks?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Help: Splitting large workbook into separate workbooks based on R column value.

    Two things, first you need a macro to merge all 10 workbooks into a single sheet. Then you need a macro to parse out that one resulting sheet into 83 individual sheets.

    'WORKBOOKS TO 1 SHEET STACKED
    Here's a macro for collecting data from all files in a specific folder.The parts of the code that need to be edited are colored to draw your attention.

    =================

    'SHEET1 TO MANY SHEETS
    Here's a macro for parsing rows of data from one sheet to many sheets named for the same values in a specific column.It not only can parse the rows, it can create the sheets if they are missing. There is a sample sheet there where you can test this out.
    Last edited by JBeaucaire; 12-27-2019 at 10:29 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

+ 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