Parsing data into Multiple Workbooks & Worksheets

[uncled]

I've got a worksheet with a large number of records that I need to break out by 1) Account Owner and 2) Customers so I end up with a separate Workbook for each Account Owner with separate worksheet for each Customer.

I can do this semi-automatically via Pivot Table - Show Pages, and then drilling down to produce the worksheets, however, I then have to rename each worksheet. The data consists of around 70 Account Owners and 500 Customers and a total of 30000 records, so this would take quite a bit of time.

Wondering if there is a way to cycle through a worksheet and split the data onto separate workbooks and worksheets.

thanks!
-ue

 

[xlbo]

Yup - using VBA

Would caution that this should only be used for reporting rather than storing the data (although from th elook of it that is what you are doing)


Code should be pretty straightforward but there is a seperate forum for VBA questions. Please post in Forum707 - or indeed have a search through some of the older posts there - pretty sure there will be numerous examples to get you started...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

Well, here we are in Forum707.

"Wondering if there is a way to cycle through a worksheet and split the data onto separate workbooks and worksheets."

dim r as range, wb as workbook
for each r in SomeColumnRange
  if [r.value meets some criteria] then
     SomeDefinesRange.Copy
     set wb = workbooks.add
     with wb
       .sheets(1).[A1].PasteSpecial xlPasteAll
       .saveas(NewWorkbookPathAndName)
       .close
     end with
  end if
next

as a template.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

D'OH! Someone hand me a coffee!!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

I've been sipping my French Roast all morning. ;-)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

lol - do you not get good Columbian roast there Skip ;-)

So that this doesn't go completely off topic - Skip's template code should work fine - you just need to plug in your own ranges and criteria

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question