Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Table to Sheets (Excel 2003 sp1)

    I have a worksheet with a pivot table to track vacation, holiday, etc time off for employees set up like this:
    Pagefield = Employee
    Row = Time Off Code
    Row = Date
    Data = Sum of Hours
    Selecting an employee in the page field shows the stats from the pivot table & also does a vlookup on another sheet for the employee name and based on that displays how much time they started with for the year, how much time taken and how much left.
    I'm trying to create a worksheet for each person, then mail it to them. I see how to create a separate worksheet for each employee using the page field...
    ActiveSheet.PivotTables(1).ShowPages Pagefield:="Employee"
    but this only puts the pivot table data on the new sheet, not all the data. How can I make it take all the data from the page to a new page? Thanks as always. You guys (and gals( are great! I'm really learning alot.

  2. #2
    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: Pivot Table to Sheets (Excel 2003 sp1)

    If you want the pivot and the underlying data, you will have to extract the underlyiing data.

    The code in <post:=511,500>post 511,500</post:> and the responses uses adv filtering to extract each item into a separate sheet. You could add summary to each sheet (copied from the pivot) or create individual pivot tables in the individual data sheets

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table to Sheets (Excel 2003 sp1)

    I should have mentioned that the additional data being displayed is not part of the underlying data for the pivot table. It's on another worksheet downloaded monthly from a main frame that's used to calculate how much time each person gets based on start dates.

  4. #4
    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: Pivot Table to Sheets (Excel 2003 sp1)

    I fail to see the difference of whether it is part of the pivot source or not.

    My impression was that you wanted to extract particular records from this data to a separate worksheet for each person. The code does that by using autofilter. It does not matter if it is part of the pivot data.

    Though, perhaps, I don't understand and you should elaborate on what you have, what you want, and how we can help.

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table to Sheets (Excel 2003 sp1)

    I'm sorry I wasn't clear. I attached the file. What I am trying to do is to create a separate sheet for each emp that shows what time was taken (vacation, holiday, etc.) and the date. Then using the allocation data from the Tenure dates worksheet, show how much time has been used and how much they have remaining. I know I could just copy and paste the data into each worksheet after they were generated; but I hate the idea of hardcoding info. I would appreciate any help you can provide.
    I'd also like to ask if anyone can steer me to some good websites where I can learn more about VBA. I can do the simple stuff, but need to know alot more for the things my boss has in mind.

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

    Re: Pivot Table to Sheets (Excel 2003 sp1)

    Try this macro:

    Sub PvtTblPgs()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem

    Set ws = Worksheets("Emp")
    Set pt = ws.PivotTables(1)

    ' Get rid of missing items
    pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
    pt.PivotCache.Refresh

    ' Loop through page fields
    Set pf = pt.PageFields(1)
    For Each pi In pf.PivotItems
    pf.CurrentPage = pi.Name
    ' Create new sheet
    ws.Copy After:=Worksheets(Worksheets.Count)
    Worksheets(Worksheets.Count).Name = pi.Name
    Next pi

    Set pi = Nothing
    Set pf = Nothing
    Set pt = Nothing
    Set ws = Nothing
    End Sub

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

    Re: Pivot Table to Sheets (Excel 2003 sp1)

    I'd get a good book on Excel VBA. I like the books by John Walkenbach.

  8. #8
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table to Sheets (Excel 2003 sp1)

    The code (of course) works like a charm. Thanks so much! Also-thanks for steering me to John Walkenbach's site. I took a quick look at it and think it may be just what I need.

Posting Permissions

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