Bacon Bits:

If you've been following the blog, you'll know that it's Pivot Table week. That's right. All in honor of the upcoming Independence Day holiday.

Here's another dose of PivotTable awesomeness. (That's right Kusleika, I said 'dose'. You don't own that word.)

Today I'll show a trick I often use to explode a dataset into separate workbooks – using a pivot table.

The sample you see here is small snippet of a 50,000 row dataset. You've been asked to create a separate workbook for each market in this dataset.

At this point, you figure you have three choices:

• Manually Copy and paste each market's data into its own workbook
• Create some VBA to somehow filter each market using autofilters
• Hit your nose with a hammer

Although you do have those choices, here is the pivot table route.

Step 1: Create a pivot table from your dataset.

Place the field you need to use as the group dimension (in this case market) into the Page/Filter field. Place the count of Market into the data field.

Depending on which version of Excel you have, your pivot table should look something like this

Now – we all know that you can manually select a Market in the Page/Filter field, and then double-click on the Count of market.

This will give a new tab containing all the records that make up the number you double-clicked.

Kinda like this:

You can imagine how you could do this for every market in the Market field and save the resulting tabs to their own workbook.

Keep this in mind as you go to step 2.

Step 2: Paste this VBA code into a new module.

This procedure will go through each item in your chosen page field and essentially call the 'ShowDetails' function for you, creating a 'raw data' tab.

The procedure then saves that 'raw data' tab to a new workbook.

Sub ExplodeTable()
Dim PvtItem As PivotItem
Dim PvtTable As PivotTable

'Change variables to suit your scenario
    Const strFieldName = "Market"   '<–Change Field Name
    Const strTriggerRange = "B4"   '<–Change Trigger Range

'Set the pivot table name if needed
    Set PvtTable = ActiveSheet.PivotTables("PivotTable1") '<–Change PivotTable Name if Needed

'Start looping through each item in the selected field
    For Each PvtItem In PvtTable.PivotFields(strFieldName).PivotItems
        PvtTable.PivotFields(strFieldName).CurrentPage = PvtItem.Name
        Range(strTriggerRange).ShowDetail = True

        'Name the temp sheet for easy cleanup later
        ActiveSheet.Name = "TempSheet"

        'copy data to new workboook and delete the temp sheet
        ActiveSheet.Cells.Copy
        Workbooks.Add
        ActiveSheet.Paste
        Cells.EntireColumn.AutoFit

        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & PvtItem.Name & ".xls"
        ActiveWorkbook.Close
        Sheets("Tempsheet").Delete
        Application.DisplayAlerts = True

    Next PvtItem

End Sub

Step 3: Be sure to change the constants and other variables to suit your scenario
Be sure to change these constants and variable if needed.

• Const strFieldName
  The field name is the name of the field you want to separate the data by. (the field you put in the Page/Filter area of the pivot table)
  
• Const strTriggerRange
  The trigger range is essentially the range that holds the one number in the pivot table's Data area. For example, if you look at the screenshot in Step 1, you'll see the trigger cell in A4 for the Excel 2007 version. For Excel 2003 it would be B4.

Step 4: Run it!

Try the sample file. Run the ExplodeTable Macro

******Update*****

Jan Karel correctly points out that you can also create new tabs for each item in your page field by using the Show Pages functionality.

First, add a field to the Page/Filter area.  In our example, we would add Market

Excel 2003:  Open the PivotTable toolbar and click the dropdown.  Choose Show Pages and click OK. Excel will add a new tab for each market, each containing a pivot table.  Explained here for Excel 2003:  http://www.mrexcel.com/tip135.shtml

Excel 2007:  Click on your pivot table to see the PivotTable Tools Options tab of the ribbon. Open the dropdown next to the Options icon on the left side of the ribbon. Choose Show Report Filter Pages and click OK.

• Previous Entry: Creating a Frequency Distribution with a Pivot Table
• Next Entry: Cut the Size of Your Pivot Table Workbooks in Half