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.

4 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.

  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 »


Get every new post delivered to your Inbox.

%d bloggers like this: