VBA GetPivotData for variable number of rows

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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?

Reply | Quote

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 🙂

Reply | Quote

32965-EQOutputSplitv5

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

Reply | Quote

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

Reply | Quote

Fantastic!!! Thank yo so much!

Reply | Quote

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

Reply | Quote

Jeremy,

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

Reply | Quote

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 🙂

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

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/showthread//151840-Pivot-Table-Report-Filter-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

Reply | Quote

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/showthread//151840-Pivot-Table-Report-Filter-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!

Reply | Quote

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!

Well… Copy 2 was a bit of a hit and miss I think. I thought that I fixed the validation issue as it worked for me but am not sure if it worked for him though. Changing the code from macro to selection change backfired completely. I’m thinking that this could work for him as long as he is not trying to manipulate pivots in other way than changing cell A1 AND the code is triggered manually (button or something). I wanted to error proof it somehow but “On Error Resume Next” doesn’t seem to be the option here, it just gets funny. No idea how to solve this, but if others are having same problem then maybe it is indeed supercomplicated or impossible?

Reply | Quote

Well… Copy 2 was a bit of a hit and miss I think. I thought that I fixed the validation issue as it worked for me but am not sure if it worked for him though. Changing the code from macro to selection change backfired completely. I’m thinking that this could work for him as long as he is not trying to manipulate pivots in other way than changing cell A1 AND the code is triggered manually (button or something). I wanted to error proof it somehow but “On Error Resume Next” doesn’t seem to be the option here, it just gets funny. No idea how to solve this, but if others are having same problem then maybe it is indeed supercomplicated or impossible?

OK, in that case let’s leave this thread if you’re happy with the ideas you’ve got for your original problem, and keep updates to the other thread. If the guy who raised the original request is still having problems I’ll have another look and put any updates in his thread!

Reply | Quote

OK, in that case let’s leave this thread if you’re happy with the ideas you’ve got for your original problem, and keep updates to the other thread. If the guy who raised the original request is still having problems I’ll have another look and put any updates in his thread!

sure thing

Reply | Quote