Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Separating data into worksheets (2003)

    Hi All,

    In another forum, there was a query regarding separating a bunch of addresses all contained on 1 sheet so that all addresses having the same state would be on their own sheet.

    See http://exceltips.vitalnews.com/Pages/T0033...Worksheets.html.

    The solution in VBA created a pivot table to create a list of unique states and then looped thru the entries in the pivot table to use as a filter criteria back on the original spreadsheet; the filtered data were then copied to a new sheet.

    If one has a pivot table with a count of addresses for each state, which could easily be done, one could click on that count in the pivot table and a new sheet would automatically be created in Excel. So I'm wondering if this is possible in VBA - loop thru the entries in the pivot table and "click" on the count for each row to let Excel create the sheets per unique state?

    TIA

    Fred

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Separating data into worksheets (2003)

    You can create a separate sheet for each value of a page field in a pivot table interactively (click Pivot Table on the Pivot Table toolbar and select Show Pages...) or using the ShowPages method of the PivotTable object (look up ShowPages in the VBA help).

    The code in <post:=594,518>post 594,518</post:> shows how to loop through the items of a page field to create a separate workbook for each item.

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Separating data into worksheets (2003)

    Thanks for the response, Hans. I guess your way is the most efficient.

    But I'm still wondering if you can do the equivalent of clicking on the "count" as you loop thru the rows of the pivot table. For example, I may want to set some criteria as to whether to even create a new sheet or file.

    Fred

  4. #4
    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: Separating data into worksheets (2003)

    Assuming a simple layout like you describe, this should work:
    <pre> Dim pt As PivotTable, pi As PivotItem
    Set pt = ActiveSheet.PivotTables(1)
    For Each pi In pt.RowFields(1).PivotItems
    pi.DataRange.ShowDetail = True
    Next pi
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Separating data into worksheets (2003)

    Thanks Rory.

    I've never done any programming with pivot tables. Looks simple enough for what I wanted to do.

    Fred

  6. #6
    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: Separating data into worksheets (2003)

    >>"I've never done any programming with pivot tables"
    Count yourself lucky. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> There are lots of gotchas and oddities and the help files can be fairly unhelpful in this area!
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    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: Separating data into worksheets (2003)

    You might check out MS MVP Debra Dagliesh's Contextures -- Excel Tips and Techniques page as she has many examples of pivot table code (as well as programming other aspects of excel).

    Steve

Posting Permissions

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