Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Vancouver, BC
    Posts
    100
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to Divide Workbook into saved sub-workbooks

    I'm trying to find a macro that will streamline a very tedious job.

    Each month, I get data for about 15 newspaper distributors. Each distributor has a large number of routes. They pay carriers extra amounts for special things added to the paper on a given day - a promotional flyer for example. At the end of each month, I pull data from our data base for each of these promotional pieces. So each month I'll have about 5 - 10 Excel workbooks, depending on the number of promotional pieces that month.

    I've attached a small example of what each file looks like. The problem is that I have to then take that file, and then break it into 15 different files - each one containing only the information for that particular distributor. Each distributor has hundreds of routes. So what I currently wind up doing is creating 15 copies of the file attached, then go into each one and laboriously delete all the rows in it except for a single distributor. Even with macro buttons to speed that process, it still takes a long time. We will be expanding soon to as many as 25 distributors, which will make this drudge work even longer each month.

    Is there a way to create a macro that can be run on a file such as the one attached, which would create a new workbook for each of the distributors listed, and then delete all the rows above and below that particular distributor? To make matters more complicated, the number of rows for each distributor can vary from month to month.

    Any ideas on how to start on this would be appreciated.

    Thanks,

    John DeA

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Macro to Divide Workbook into saved sub-workbooks

    I'm sure someone here will come up with suitable code for doing this, but, as a matter of interest, what sort of database does the information come out of? It may be easier to amend the extract process to do this rather than doing it in Excel afterwards.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro to Divide Workbook into saved sub-workbooks

    The code in Printout of each item from an Autofilter list (All) does most of what you want. It sets it up to printout each item.

    You can change the "printout" line in the code to:

    create a new sheet
    copy and paste to that sheet
    and then save/close the workbook.

    Hope this gets you started...
    Steve

  4. #4
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Vancouver, BC
    Posts
    100
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Divide Workbook into saved sub-workbo

    The info comes from a customized database program called Matrix, designed as a circulation database for newspapers. My company was the one it was designed for, so we're essentially using Version 1.0, much to my eternal dismay.

    We use Hummingbird as our reporting tool. I could certainly pull the data from Hummingbird into separate Excel workbooks (actually as csv files), but with Hummingbird's interface, that would require me pulling the data 15 separate times, which would defeat the purpose of saving time.

  5. #5
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Vancouver, BC
    Posts
    100
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Divide Workbook into saved sub-workbo

    Thanks - I've taken a quick look at this and it looks very promising. I'll give it a shot and get back to you!

Posting Permissions

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