Partitioning Data into Multiple Workbooks

I’ll return to the worksheets-to-table collation shortly…

I was diverted by a problem that my wife came up with: how to partition data in a single workbook into multiple workbooks, depending on the value in a particular ‘key’ column. Here’s the kind of thing we’d have as the input worksheet:

The data is sorted on the first, Key, column. There could be multiple data columns.

What we want is to create a workbook AAA.xls, containing the first 7 data values; a workbook BBB.xls, containing the next 3 data values; a workbook CCC.xls, containing the last 5 data values. And so on.

Obviously the numbers of workbooks and values involved could be large – so we’re not going to do this using copy-and-paste (and this is a VBA blog, after all!). Incidentally, this is in v2003, not that it matters.

So we need some way of finding where the Key values change. We could just iterate through the cells, but that’s rather plodding. We have, in the normal user interface, the command >Edit >Go To Special >Column Differences. If we start off with A:A selected, it changes the selection to A2:A16, then to A9:A16, and so on. If we do it on the last block, with A12:A16 selected, we get an error.

The key bit of VBA is:


where ActiveCell is the first cell of the selected Range. Selection.Row is then the start of a chunk. Doing it again gives us the start of the next chunk, and thus the end of the preceding chunk.

So we can just cycle through the chunks in a Do While True loop, exiting when we get an error on the last chunk (remembering to copy that last chunk).

I added a Control worksheet with a command button, and prompted for the source worksheet name (in case there are multiple ones). I didn’t copy over the header row for columns B+, but that wouldn’t be difficult to do.

That seems to do the trick: the code module is here, if you’re interested. There are doubtless other ways of doing this, but my solution got the required Brownie points from Liz.

6 Responses to “Partitioning Data into Multiple Workbooks”

  1. 1 Mike Alexander May 19, 2010 at 9:18 pm

    How unbelievably cool! I’ve done this same task for years by auto filtering unique values from a key column, then iterating through them.

    I’m embarrased now….This is so much easier!

    Thanks Roy.

  2. 2 hydroxycut April 25, 2014 at 6:25 pm

    Fantastic beat ! I wish to apprentice while you amend your website, how could i subscribe for a weblog site?
    The account aided me a appropriate deal. I had been tiny
    bit familiar of this your broadcast offered brilliant transparent concept

  3. 3 BioScience Labs October 3, 2014 at 1:05 am

    Please let me know if you’re looking for a article
    author for your blog. You have some really great posts
    and I believe I would be a good asset. If you ever want to take some of
    the load off, I’d love to write some content for your blog in exchange
    for a link back to mine. Please blast me an e-mail if
    interested. Regards!

  1. 1 Divide your data set into workbooks « Excel Tips Monster Trackback on May 19, 2010 at 9:18 pm
  2. 2 Sub-sequence Cursor « Roy MacLean's VBA Blog Trackback on May 20, 2010 at 8:35 am
  3. 3 GoTo Special Non Blank Cells » Bacon Bits: Trackback on June 4, 2010 at 9:31 am

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

May 2010
« Apr   Jun »

%d bloggers like this: