• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to loop through successive Pivot Table Report Filter values

David Evans

David Evans

Active Member
Has anyone used VBA to loop through successive values in the report filter field? If so, any insight would be appreciated.
 
Luke M said:
Not a direct answer, but here's my go-to reference for when I need to deal with ranges in a PT. Hope it helps.
http://peltiertech.com/referencing-pivot-table-ranges-in-vba/
Click to expand...
Thanks Luke - I like that one also.
I've come up with a simple piece of code which does what i need
Code: 
Sub Pivot_Loop()
  Dim pt As PivotTable
  Dim pi As PivotItem
  Set pt = ActiveSheet.PivotTables("ClientAccounts")
  For Each pi In pt.PageFields("Client ID").PivotItems
      pt.PageFields("Client ID").CurrentPage = pi.Name
     
      ' your code to do whatever you need goes here

  Next pi
End Sub

Now what I need is to get the Grand Total for the Table, which Peltiertech doesn't cover .... but will soon find it
 
jeffreyweir said:
Are you trying to filter the PivotTable for each different client, Dave?
Click to expand...
Yes - I loop through each particular client file - perform a calculation and write a copy to a file before moving to the next client
Code: 
Sub Pivot_Loop()
  Dim pt As PivotTable
  Dim pi As PivotItem
  Dim rGrandTotal As Range
 
  Set pt = ActiveSheet.PivotTables("ClientAccounts")
  For Each pi In pt.PageFields("Client ID").PivotItems
      pt.PageFields("Client ID").CurrentPage = pi.Name
     
        With pt.TableRange1
            Set rGrandTotal = .Cells(.Cells.Count)
            If rGrandTotal <= 100000000 Then
                DataWritetoSummary False
                SaveRelationshipCopy
                CopyRange
                UnhideRandC
            End If
           
        End With
     
     
  Next pi
End Sub

It's working now - but I have a lot more testing to get done tomorrow.

Alas, this is a one of those projects where the end-users want to retain the look and feel of what they had, but want it automated. If you started from scratch you'd design it in a very different way - you might not even use excel ...

I liken it to someone who has a horse and cart who wants you to make it into a motor car, while keeping the horse and the cart as part of the running gear ....;)
 
Hi David ,

You are right about this :
I liken it to someone who has a horse and cart who wants you to make it into a motor car, while keeping the horse and the cart as part of the running gear
Click to expand...
I'd like to add :

Most times , the problem is not in that ; the problem is that they want the entire contraption to run faster , much faster !

Narayan
 
The SubRoutine entitled Pivot Loop above is designed to loop through a range of values (which are actually text, not numeric values) that are in the format e.g. "0011".
These are not necessarily sequential, i.e. there may be some missing values from a normal arithmetical sequence. The loop works perfectly, wait for it, until it arrives at "0154" (which happens to be the 101st item in the list) and then it fails ... any ideas?

Are there any limitations I'm running up against, by any chance?
 
Last edited:
jeffreyweir said:
Dave, my man....can you post a sample file?
Click to expand...
Jeffrey, Me Old China!

Its going to take me a bit of time to sanitize it for data and reduce the size of the file, but I know that nothing beats looking at the Full Monty!

As I've noted earlier, this project is being driven by legacy issues and an unwillingness to use a screw instead of a nail ...
 
You must log in or register to reply here.
Back
Top