Pivot Tables: error in 'Field.CurrentPage = NewCat'

[Paul_Hossler]

Not tested

Set pt = Worksheets("New Business").PivotTables("PivotTable7")
Set Field = pt.PivotFields("Job No.")
NewCat = Worksheets("New Business").Range("B1").Value


Set pt = Worksheets("New Business").PivotTables("PivotTable1")
Set Field = pt.PivotFields("Job No.")
NewCat = Worksheets("New Business").Range("B1").Value

[Ray707]

Hi Paul, thanks for the response. I tried that but unfortunately only pivot table 1 updates, pivot table 7 doesn't update

[p45cal]

Instead of using:
pt.RefreshTable
use:
pt.PivotCache.Refresh
All pivots based on the same PivotCache will be refreshed.
So onto the question, are both pivot tables based on the same PivotCache?
If you haven't got many pivot tables in the workbook you might be lucky and be able to find out this way; in the Immediate pane of the VBE, while the appropriate workbook is active, type:
?activeworkbook.PivotCaches.Count
and press Enter. If you get the answer 1 there's nothing to do except replace .RefreshTable with .PivotCache.Refresh.
If you get more than 1 as an answer then type this into the Immediate pane:
? ActiveSheet.PivotTables("PivotTable7").pivotcache is ActiveSheet.PivotTables("PivotTable1").pivotcache
and press Enter. You'll get True if the two pivots share the same PivotCache and False if they don't. If they don't then you have to update them individually and add the line
ActiveSheet.PivotTables("PivotTable1").pivotcache.Refresh

[Ray707]

I figured it out! Basically you have to repeat the bold part twice in the code, one for each pivot table:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
'This line stops the worksheet updating on every change, it only updates when cell
'A1:B2 is touched
If Intersect(Target, Range("A1:B2")) Is Nothing Then Exit Sub
 
'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String
 
'Here you amend to suit your data for first pivot table
Set pt = Worksheets("New Business").PivotTables("PivotTable7")
Set Field = pt.PivotFields("Job No.")
NewCat = Worksheets("New Business").Range("B1").Value
 
'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters


Field.CurrentPage = NewCat
pt.RefreshTable
End With


'Here you amend to suit your data for second pivot table
Set pt = Worksheets("New Business").PivotTables("PivotTable1")
Set Field = pt.PivotFields("Job No.")
NewCat = Worksheets("New Business").Range("B1").Value
 
'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters


Field.CurrentPage = NewCat
pt.RefreshTable
End With
 
End Sub

[Ray707]

Instead of using:
pt.RefreshTable
use:
pt.PivotCache.Refresh
All pivots based on the same PivotCache will be refreshed.
So onto the question, are both pivot tables based on the same PivotCache?
If you haven't got many pivot tables in the workbook you might be lucky and be able to find out this way; in the Immediate pane of the VBE, while the appropriate workbook is active, type:
?activeworkbook.PivotCaches.Count
and press Enter. If you get the answer 1 there's nothing to do except replace .RefreshTable with .PivotCache.Refresh.
If you get more than 1 as an answer then type this into the Immediate pane:
? ActiveSheet.PivotTables("PivotTable7").pivotcache is ActiveSheet.PivotTables("PivotTable1").pivotcache
and press Enter. You'll get True if the two pivots share the same PivotCache and False if they don't. If they don't then you have to update them individually and add the line
ActiveSheet.PivotTables("PivotTable1").pivotcache.Refresh

Hey buddy thanks for this, I managed to figure it out in the end (see above) but I'll def take a look at your solution to see how it works once I get this freakin' report out of the way

I'll rep you both when I'm recharged

[p45cal]

Then you should be able to shorten the entire code to:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim NewCat As String
If Intersect(Target, Range("A1:B2")) Is Nothing Then Exit Sub
NewCat = Worksheets("New Business").Range("B1").Value
For Each ptName In Array("PivotTable7", "PivotTable1")
  With Worksheets("New Business").PivotTables(ptName)
    .PivotFields("Job No.").CurrentPage = NewCat
    .RefreshTable
  End With
Next ptName
End Sub

If the sheet called New Business is the same sheet that the code is in (I strongly suspect it is) you don't need to explicitly refer to it in the code so the code becomes:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim NewCat As String
If Intersect(Target, Range("A1:B2")) Is Nothing Then Exit Sub
NewCat = Range("B1").Value
For Each ptName In Array("PivotTable7", "PivotTable1")
  With PivotTables(ptName)
    .PivotFields("Job No.").CurrentPage = NewCat
    .RefreshTable
  End With
Next ptName
End Sub

Finally, do you really need to do this on each selection change?
I would try putting it into the Worksheet_Change event instead.

[Paul_Hossler]

@Ray707

1. I saw in your earlier post that you were using Worksheet_Change event

Did you change it back?

2. Are you sure you want A1:B2? IIRC you enter a number into A1, and B1 is a VLookup. Probably no matter but if you change the text in A2, the event will fire most likely unnecessarily

3. If the number of PTs could change (like it did), you could use something like

Private Sub Worksheet_Change(ByVal Target As Range)
Dim pt as PivotTable
Dim NewCat As String

If Intersect(Target, Range("A1:B2")) Is Nothing Then Exit Sub

NewCat = Range("B1").Value

For Each pt In PivotTables
On Error GoTo NextPT
  With pt 
    .PivotFields("Job No.").CurrentPage = NewCat
    .RefreshTable
  End With

NextPT:
    On Error Goto 0
NextEnd Sub

That way you wouldn't need to modify the code if you add another PT, or rely on PTs having a certain name (I usually rename mine to something meaningful)

[Ray707]

Finally, do you really need to do this on each selection change?
I would try putting it into the Worksheet_Change event instead.

Yes that worked, thank you!

And I've just figured out the difference between selection change and worksheet_change, and I agree, the latter is better!

[Ray707]

...

Hi buddy, in terms of Q1, I've been dipping in and out of the file & code quite a bit over the past few days so my guess is I accidentally changed it back to selectionChange after playing around with the code and exploring!

In terms of A1:B2, you are correct; I'm not sure why my predecessor chose A1:B2 but I don't want to make too many changes to the file as it's not my file and I'm scared I'll break something! The team rely on it so heavily I poop myself every time I go in it and save changes tbh Cells A2 and B2 don't have anything in them anyway and likely won't have so I think we're all good!

As for your code, that also worked perfectly as well so thank you!

[Ray707]

I can't give reputation as I need to spread it before giving it to you guys again

[H2V]

Hola Ray707.
He sufrido este mismo problema desde hace unos meses.


Para solucionarlo he hecho 2 cosas:
1: El campo currentPage debe ser tipo Variant
2: Escribir la sentencia completa obviando los Active... y los With .


Prueba lo siguiente

Dim NewCat As Variant
....


Worksheets("Client").PivotTables("PivotTable7").PivotFields("TEBUD").ClearAllFilters
Worksheets("Client").PivotTables("PivotTable7").PivotFields("TEBUD").CurrentPage = NewCat

[H2V]

Hello Ray707.
I have had this same problem for a few months.
To fix it I have done 2 things:
1: The currentPage field must be Variant type
2: Write the complete sentence ignoring the Active... and With .


try the following

Dim NewCat As Variant
....


Worksheets("Client").PivotTables("PivotTable7").PivotFields("TEBUD").ClearAllFilters
Worksheets("Client").PivotTables("PivotTable7").PivotFields("TEBUD").CurrentPage = NewCat