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

[Ray707]

Hello,

I have a VBA that automatically updates a pivot table filter on my worksheet based on the value you enter in cell A1 in that sheet. Or at least it should do. The code used to work but now I get an error message saying:

'run time error 1004: application-defined or object-defined error'

When I click on 'debug' it highlights the bold part in yellow: [Red Line:SamT)




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
Set pt = Worksheets("Client").PivotTables("PivotTable7")
Set Field = pt.PivotFields("TEBUD")
NewCat = Worksheets("Client").Range("B1").Value

'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters


Field.CurrentPage = NewCat
pt.RefreshTable
End With

End Sub

does anyone know why this is happening and how to fix it? :thumb

[Ray707]

can anyone help please? :(

[SamT]

Dim Field As PageField
Set Field = pt.PageFields("TEBUD")

[Paul_Hossler]

I'd use Change instead of SelectionChange




Private Sub Worksheet_Change(ByVal Target As Range)



Maybe attach a small sample workbook also

I'd add Application.EnableEvents = False also

[Ray707]

Dim Field As PageField
Set Field = pt.PageFields("TEBUD")

Thanks for the response. I tried amending the code to incorporate your suggestion but it gave me an error saying 'user-defined type not defined'. Here's the code I implemented:



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 PageField
Dim NewCat As String

'Here you amend to suit your data
Set pt = Worksheets("Client").PivotTables("PivotTable7")
Set Field = pt.PageFields("TEBUD")
NewCat = Worksheets("Client").Range("B1").Value

'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters


Field.CurrentPage = NewCat
pt.RefreshTable
End With

End Sub

[Ray707]

I'd use Change instead of SelectionChange




Private Sub Worksheet_Change(ByVal Target As Range)



Maybe attach a small sample workbook also

I'd add Application.EnableEvents = False also

Yes sure. See the following link (I tried to attach but it didn't work): https://easyupload.io/cy2uqm

I have had to delete quite a lot of data and sheets to ensure it has no sensitive data.

Nevertheless in the file you will see a pivot on the first worksheet called 'Client'. You will see the pivot from cell L10 onwards under 'details of actuals posted'. This pivot should be updating when the value in cell A1 is changed in the same sheet (which then generates a client name in cell B1 that I think the code picks up in order to amend the pivot [the font in cell B1 is white so please click on it to see the formula]- this was done my by predecessor so I'm trying to fix it!). Specifically, the filter called 'TEBUD' should be updating in the pivot (cell M11).

The error I get when implementing the code in my first post is related to this part and I'm not sure why:


Field.CurrentPage = NewCat

The amended raw data is in the 'YTD raw data' tab. Any help would be appreciated :thumb

p.s. I thought SelectionChange was what I needed as I only want the pivot to update when I amend one cell?

[p45cal]

It goes wrong here because the pivot table's source data is full of #REF errors, especially column G of the source data whose formula includes #REF.

[Ray707]

I had to delete a lot of data, hence why #REF comes up. In the original file there are none of those. I was hoping someone could help by assessing the code and its logic, without the need to look at the actual data :(

[Paul_Hossler]

28309

As p45cal says, the #REF's did not help

I had to enter some dummy data to get anything to happen




Option Explicit


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

'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
'Here you amend to suit your data
Set pt = Worksheets("Client").PivotTables(1)
Set Field = pt.PivotFields("TEBUD")
NewCat = Worksheets("Client").Range("A1").Value

'This updates and refreshes the PIVOT table
Application.EnableEvents = False
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With
Application.EnableEvents = True
End Sub

[p45cal]

If Paul hasn't sorted it for you (I didn't see much wrong with the code and since it's guaranteed to fail if the value in A1 isn't one of the values in the pivot table, but you say it used to work, I didn't look hard) then supply a file without a load of errors in.
Personally, instead of cell A1, I'd add a slicer to the pivot for that field, then move it near cell A1.

[Paul_Hossler]

If Paul hasn't sorted it for you (I didn't see much wrong with the code and since it's guaranteed to fail if the value in A1 isn't one of the values in the pivot table, but you say it used to work, I didn't look hard) then supply a file without a load of errors in.

Personally, instead of cell A1, I'd add a slicer to the pivot for that field, then move it near cell A1.


... much ...???? :rotlaugh:


Agree, it's not very robust or bullet proof

Apparently the client number 1 - 36 is entered, instead of the names I used

Adding a check or being careful, you can get by.

A slicer or at least Data Validation on A1 would help

[p45cal]

Paul, I wasn't talking about your code, but Ray707's! Although I can see I didn't make it crystal clear my mentioning 'it used to work' may have been a pointer.

[Ray707]

Thanks for your assistance guys. For some god damn reason it still isn't working for me :banghead:

I've taken a screenshot of what I see on my file to hopefully help: https://ibb.co/Kyk3GK0

If you look at cell M11 you'll see 'Bacardi - Kaitlin'; this is the same value as in cell B1, and cell B1 updates via a VLOOKUP to sheet 'T&E Summary' when a number is entered in cell A1. In other words, when cell A1 is changed to '2', it brings over the client into cell B1 from 'T&E Summary', and I'm hoping to update the pivot table filter (M11) with that clients actuals.

I had a couple of attempts using the code Paul provided just above but amended it to suit my file. The first attempt was with the code below (bold is where I edited to suit my file):



Option Explicit


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

'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
'Here you amend to suit your data
Set pt = Worksheets("Client").PivotTables(7)
Set Field = pt.PivotFields("TEBUD")
NewCat = Worksheets("Client").Range("B1").Value

'This updates and refreshes the PIVOT table
Application.EnableEvents = False
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With
Application.EnableEvents = True
End Sub

In the code, I amended .PivotTables to 7 instead of 1, as that is the name of the pivot table in my file:

28312

I also amended .Range to B1 as this where the pivot filter should be referencing in order to update the pivot.

Unfortunately the code did not work.

I then tried another variation. In this one I changed .PivotTables to .PivotTables("PivotTable7") to see if that would work:



Option Explicit


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

'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
'Here you amend to suit your data
Set pt = Worksheets("Client").PivotTables("PivotTable7")
Set Field = pt.PivotFields("TEBUD")
NewCat = Worksheets("Client").Range("B1").Value

'This updates and refreshes the PIVOT table
Application.EnableEvents = False
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With
Application.EnableEvents = True
End Sub

Unfortunately this did not work either. Any help would be appreciated and thanks once again guys :thumb

[p45cal]

The change to looking at cell B1 is fine, but now you have to make sure that cell B1's formula returns EXACTLY (no extra spaces etc.) one of the choices in pivot table page field (TEBUD). So make sure the lookup table is correct.
Otherwise reattach the file with that look up table included - at least columns 1 and 7 of it, and valid data in for the pivot, even if it's only fake data.

[Ray707]

I've just realised what it is! When there are no actuals in the T&E Summary tab (i.e. no raw data for that client) then the code generates an 'error'. So say I type the number 3 in cell A1; if there is no raw data for client 3 in the T&E Summary tab then the pivot has no data to refresh with, hence why it runs and generates an 'error' message. When I type in a client number that does have data in the T&E Summary tab the code successfully runs and refreshes the pivot with that data.

This has been such a fantastic learning curve for me, I cannot thank you guys enough!

I wonder if there's a way to amend the code to say 'if there's no data in the T&E Summary tab for that client then make the pivot table blank' because at the moment it's posting all of the raw data in the pivot when the code's breaking.

If you guys know of a fix then I'm all ears, but at least I know the code works! :thumb

[Ray707]

p.s. it says I've given out too much reputation- Paul I'll give it to you when I'm recharged :thumb

[Paul_Hossler]

Added a little more error checking

Used a dummy Number-Client Name table

Added some comments

Faked data

28314





Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)

'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As Variant


'This line stops the worksheet updating on every change
If Target.Address <> "$A$1" Then Exit Sub

'Here you amend to suit your data
'!!!! The PT is NAMED "PivotTable7" or it is the FIRST PT on the worksheet
Set pt = Worksheets("Client").PivotTables("PivotTable7")
Set pt = Worksheets("Client").PivotTables(1)
Set Field = pt.PivotFields("TEBUD")
NewCat = Worksheets("Client").Range("B1").Value

If IsError(NewCat) Then
MsgBox "Bad Client"
Exit Sub
ElseIf Target.Value < 1 Or Target.Value > 3 Then
MsgBox "Bad Client"
Exit Sub
End If

'add error handling
On Error GoTo ErrExit

'do NOT call this event again when we make Changes to this WS
Application.EnableEvents = False

'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With

Application.EnableEvents = True

Exit Sub ' don't want to run the error message below if all ok

ErrExit:
On Error GoTo 0
Application.EnableEvents = True
MsgBox "Some pivot table problem"
End Sub

[Ray707]

Thanks. I used that to create a pop up message for when there is no data :cool:

[Paul_Hossler]

Paul, I wasn't talking about your code, but Ray707's! Although I can see I didn't make it crystal clear my mentioning 'it used to work' may have been a pointer.

1. I'm not sensitive -- any 'pride of authorship' I ever had was burned out of me decades ago

2. However, I did add some data protection to the macro for Ray707

I'd still use a list of Client Names in A1 with Data Validation

[Ray707]

Hi guys, quick question, if I wanted to refresh two pivot table filters on the same sheet how would I alter the code? I tried to alter the bold part but it didn't work:



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

[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 :thumb

I'll rep you both when I'm recharged :thumb

[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! :thumb

[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 :rotlaugh: 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! :thumb

[Ray707]

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

[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