Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Lounger
    Join Date
    Jan 2013
    Location
    Kent UK
    Posts
    29
    Thanks
    5
    Thanked 0 Times in 0 Posts

    VBA GetPivotData for variable number of rows

    Hi Guys,
    Im new here, hope you can help (I heard you can) .

    So I have this code here, it works really well, retrieves data from pivot table, renames sheets etc. Problem is that each time I have more/less rows in the pivot, I have to amend it accordingly. How can i make it dynamic just so it recognises how many rows there are in the pivot and does its magic for all staff ID's (rows)?

    thanks
    Jacek

    Sub drill()
    Dim sel As Variant
    Application.ScreenUpdating = False
    Sheets("Pivot").Activate
    With Sheets("Pivot").PivotTables(1)
    sel = .GetPivotData("Count of Patient ID", "Staff ID", Range("a9").Value)
    sel = .GetPivotData("Count of Patient ID", "Staff ID", Range("a9").Value).Address
    Range(sel).ShowDetail = True
    Range("a1").Select
    End With
    NewName = Range("B2").Value
    ActiveSheet.Name = NewName
    Sheets("Pivot").Activate
    With Sheets("Pivot").PivotTables(1)
    sel = .GetPivotData("Count of Patient ID", "Staff ID", Range("a10").Value)
    sel = .GetPivotData("Count of Patient ID", "Staff ID", Range("a10").Value).Address
    Range(sel).ShowDetail = True
    Range("a1").Select
    End With
    Application.ScreenUpdating = True
    End Sub

  2. #2
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    223
    Thanks
    0
    Thanked 21 Times in 20 Posts
    Quote Originally Posted by jckplck View Post
    Hi Guys,
    Im new here, hope you can help (I heard you can) .

    So I have this code here, it works really well, retrieves data from pivot table, renames sheets etc. Problem is that each time I have more/less rows in the pivot, I have to amend it accordingly. How can i make it dynamic just so it recognises how many rows there are in the pivot and does its magic for all staff ID's (rows)?

    thanks
    Jacek

    Sub drill()
    Dim sel As Variant
    Application.ScreenUpdating = False
    Sheets("Pivot").Activate
    With Sheets("Pivot").PivotTables(1)
    sel = .GetPivotData("Count of Patient ID", "Staff ID", Range("a9").Value)
    sel = .GetPivotData("Count of Patient ID", "Staff ID", Range("a9").Value).Address
    Range(sel).ShowDetail = True
    Range("a1").Select
    End With
    NewName = Range("B2").Value
    ActiveSheet.Name = NewName
    Sheets("Pivot").Activate
    With Sheets("Pivot").PivotTables(1)
    sel = .GetPivotData("Count of Patient ID", "Staff ID", Range("a10").Value)
    sel = .GetPivotData("Count of Patient ID", "Staff ID", Range("a10").Value).Address
    Range(sel).ShowDetail = True
    Range("a1").Select
    End With
    Application.ScreenUpdating = True
    End Sub
    I'm not entirely clear which bit of code you need to repeat, but if PT is a PivotTable variable then the property PT.TableRange1 returns an Excel range that includes the range of the PivotTable report (excluding any Page Fields; there is another property TableRange2 that does include the Page Fields if you have any and need to include them in the logic).

    From PT.TableRange1 you can examine the usual Row and Rows properties to find out where the data starts (adjust to exclude headings if necessary), how many rows to process, etc.

    Does this give you a starting point?

  3. #3
    Lounger
    Join Date
    Jan 2013
    Location
    Kent UK
    Posts
    29
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hi Jeremy, thank you for your reply. My fault, I wasn't specific enough.
    The problem I have is that each week there is a different number of rows (unique "Staff ID") in the table. So far I've been dealing with it by adding or deleting this section of code here:

    Sheets("Pivot").Activate
    With Sheets("Pivot").PivotTables(1)
    sel = .GetPivotData("Count of Patient ID", "Staff ID", Range("a9").Value)
    sel = .GetPivotData("Count of Patient ID", "Staff ID", Range("a9").Value).Address
    Range(sel).ShowDetail = True
    Range("a1").Select
    End With
    NewName = Range("B2").Value
    ActiveSheet.Name = NewName

    and changing this bit Range("a9").Value to Range("a10").Value, Range("a11").Value etc. depending on how many rows there are in the table. So I would like to avoid editing the code each time my table has a different number of rows or different number of unique "Staff ID's" if you like. I was thinking of looping it and adding some sort of condition but still the Range("a9").Value is the problem here as it is static. Perhaps offset could work here but I just don't know how to make all this work together. Im still fairly new to VBA and some bits are just too complicated

    Let me know if you need me to clarify anything else or would you like to see the spreadsheet and test it yourself?

  4. #4
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    223
    Thanks
    0
    Thanked 21 Times in 20 Posts
    Quote Originally Posted by jckplck View Post
    Hi Jeremy, thank you for your reply. My fault, I wasn't specific enough.
    The problem I have is that each week there is a different number of rows (unique "Staff ID") in the table. So far I've been dealing with it by adding or deleting this section of code here:

    Sheets("Pivot").Activate
    With Sheets("Pivot").PivotTables(1)
    sel = .GetPivotData("Count of Patient ID", "Staff ID", Range("a9").Value)
    sel = .GetPivotData("Count of Patient ID", "Staff ID", Range("a9").Value).Address
    Range(sel).ShowDetail = True
    Range("a1").Select
    End With
    NewName = Range("B2").Value
    ActiveSheet.Name = NewName

    and changing this bit Range("a9").Value to Range("a10").Value, Range("a11").Value etc. depending on how many rows there are in the table. So I would like to avoid editing the code each time my table has a different number of rows or different number of unique "Staff ID's" if you like. I was thinking of looping it and adding some sort of condition but still the Range("a9").Value is the problem here as it is static. Perhaps offset could work here but I just don't know how to make all this work together. Im still fairly new to VBA and some bits are just too complicated

    Let me know if you need me to clarify anything else or would you like to see the spreadsheet and test it yourself?
    So are you saying that one week there are rows 9, 10 and 11 to process, and another week it might be rows 9, 10, 11 and 12, and the block of code you've included above would have to be repeated for each one?

    If you're willing to upload your data that would be great so that even if I can't help, someone else on this forum surely will

  5. #5
    Lounger
    Join Date
    Jan 2013
    Location
    Kent UK
    Posts
    29
    Thanks
    5
    Thanked 0 Times in 0 Posts
    EQOutputSplitv5.xlsm
    Quote Originally Posted by jeremybarker View Post
    So are you saying that one week there are rows 9, 10 and 11 to process, and another week it might be rows 9, 10, 11 and 12, and the block of code you've included above would have to be repeated for each one?

    If you're willing to upload your data that would be great so that even if I can't help, someone else on this forum surely will
    Yes, exactly that! See the attached spreadsheet. It works the treat but... Filter out one of the staff ID and it all goes pear shape

    thanks

  6. #6
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,548
    Thanks
    3
    Thanked 149 Times in 142 Posts
    Try this code which loops until it hits the total row
    Code:
    Sub drill()
      Dim sel As Variant, aRng As Range
      Application.ScreenUpdating = False
      
      Sheets("Pivot").Activate
      Set aRng = Range("A4")
      While aRng.Value <> "Grand Total"
        With Sheets("Pivot").PivotTables(1)
          sel = .GetPivotData("Count of Patient ID", "Staff ID", aRng.Value)
          sel = .GetPivotData("Count of Patient ID", "Staff ID", aRng.Value).Address
        End With
        Range(sel).ShowDetail = True
        Range("a1").Select
        ActiveSheet.Name = aRng.Value
        Sheets("Pivot").Activate
        Set aRng = aRng.Offset(1, 0)
      Wend
      
      Application.ScreenUpdating = True
    End Sub
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  7. The Following User Says Thank You to Andrew Lockton For This Useful Post:

    jckplck (2013-01-28)

  8. #7
    Lounger
    Join Date
    Jan 2013
    Location
    Kent UK
    Posts
    29
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Fantastic!!! Thank yo so much!

  9. #8
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    223
    Thanks
    0
    Thanked 21 Times in 20 Posts
    Quote Originally Posted by jckplck View Post
    Fantastic!!! Thank yo so much!
    Just as an alternative to Andrew's excellent macro, here is what was in my mind while I was pondering your problem before you uploaded the live file:

    Code:
    Sub JBDrill()
        Dim ws As Worksheet
        Dim PT As PivotTable
        Dim lngStart As Long
        Dim lngEnd As Long
        Dim lngRow As Long
        
        Set ws = Application.ThisWorkbook.Worksheets("Pivot")
        Set PT = ws.PivotTables(1)
        lngStart = PT.TableRange1.Row + 1    ' start 1 row below headings
        lngEnd = lngStart + PT.TableRange1.Rows.Count - 3 ' take off 3 to get row above totals
        
        Application.ScreenUpdating = False
        For lngRow = lngStart To lngEnd
            ws.Cells(lngRow, 2).ShowDetail = True
            ActiveSheet.Name = ws.Cells(lngRow, 1).Value
            ActiveSheet.Range("A1").Select
        Next lngRow
        Application.ScreenUpdating = True
        
        Set PT = Nothing
        Set ws = Nothing
    End Sub
    Jeremy

  10. The Following User Says Thank You to jeremybarker For This Useful Post:

    jckplck (2013-01-28)

  11. #9
    Lounger
    Join Date
    Jan 2013
    Location
    Kent UK
    Posts
    29
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Jeremy,

    Thanks a lot! It works perfect. I'm amazed by how little code it needs! I have loads to learn Thank you

  12. #10
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    223
    Thanks
    0
    Thanked 21 Times in 20 Posts
    Quote Originally Posted by jckplck View Post
    Jeremy,

    Thanks a lot! It works perfect. I'm amazed by how little code it needs! I have loads to learn Thank you
    To be honest I could have condensed it even more - but I usually prefer to err on the side of readability, especially if I'm posting for someone else!

    Note that both Andrew and I have skimped on the error handling so you should probably add some code for those nasty situations when things go wrong

  13. #11
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,548
    Thanks
    3
    Thanked 149 Times in 142 Posts
    Jeremy has an excellent point on the error handling. I can see multiple ways that all the variations shown would fail without error handling.

    For instance if you were using my code then the macro wouldn't end gracefully if the Grand Total was turned off for the last cell since my variation looks for those specific words to stop the macro. An error would also appear if a sheet name can't be assigned for some reason eg. name already exists/illegal name.

    Adding error handling code to deal with these possibilities will make your macro much more robust but will add drastically to the amount of code required for the actual task.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  14. #12
    Lounger
    Join Date
    Jan 2013
    Location
    Kent UK
    Posts
    29
    Thanks
    5
    Thanked 0 Times in 0 Posts
    I see what you mean guys. As things stand I will be the only person using it but perhaps for future reference it would be good to safeguard it somehow. Sadly I have no Idea where to start. Can you point me in the right direction? I will happily read a bit about it.

    Thanks

  15. #13
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    223
    Thanks
    0
    Thanked 21 Times in 20 Posts
    Quote Originally Posted by jckplck View Post
    I see what you mean guys. As things stand I will be the only person using it but perhaps for future reference it would be good to safeguard it somehow. Sadly I have no Idea where to start. Can you point me in the right direction? I will happily read a bit about it.

    Thanks
    There isn't really a definitive list unfortunately, because when you're programming an end-user tool like Excel the "end-user" is pretty much free to change things how they want unless you impose a repressive level of worksheet protection!

    Things to think about:
    both our macros assume that the summary sheet is going to be called "Pivot" and fail if the sheet has been renamed
    we don't check to ensure that PivotTable1 actually exists
    depending on the pivot table display settings there may be more than 1 header row
    the Grand Total summary may be displayed at the top of the table, or not at all
    there may be extra levels of summary (extra columns)

    These are just off the top of my head!

  16. #14
    Lounger
    Join Date
    Jan 2013
    Location
    Kent UK
    Posts
    29
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Guys,

    I cannot send you private messages so I am hoping you will see this post.
    I am trying my best to help one guy who wants multiple pivots to be controlled with a single cell. I got it working but there are some issues with the code. I wondered if you could have a look and see and shed some light on it http://windowssecrets.com/forums/sho...ilter-Excel-07 It works fine until he filters one of the tables "manually" the code goes pear-shape then and it all stops. I tried and tried and read about error handlers but just cannot get it to work.
    Thanks

  17. #15
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    223
    Thanks
    0
    Thanked 21 Times in 20 Posts
    Quote Originally Posted by jckplck View Post
    Guys,

    I cannot send you private messages so I am hoping you will see this post.
    I am trying my best to help one guy who wants multiple pivots to be controlled with a single cell. I got it working but there are some issues with the code. I wondered if you could have a look and see and shed some light on it http://windowssecrets.com/forums/sho...ilter-Excel-07 It works fine until he filters one of the tables "manually" the code goes pear-shape then and it all stops. I tried and tried and read about error handlers but just cannot get it to work.
    Thanks
    So were there still problems with the Copy2 version you posted yesterday? I was actually lurking in that thread for a while but I thought you'd managed to sort the problem

    I've never actually tried to program the CurrentPage setting of a PivotTable before, but a quick google shows that you are not alone having problems with this feature!

Page 1 of 2 12 LastLast

Posting Permissions

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