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

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)

Code:

---

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

can anyone help please? :(

Code:

---

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

---

I'd use Change instead of SelectionChange

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)


---

Maybe attach a small sample workbook also

I'd add Application.EnableEvents = False also

Quote:

---

Originally Posted by SamT

Code:

---

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:

Code:

---

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

---

Quote:

---

Originally Posted by Paul_Hossler

I'd use Change instead of SelectionChange

Code:

---

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:

Code:

---

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?

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.

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 :(

Attachment 28309

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

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

Code:

---

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

---

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.

Quote:

---

Originally Posted by 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.

---

... 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

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.

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):

Code:

---

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:

Attachment 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:

Code:

---

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

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.

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

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

Added a little more error checking

Used a dummy Number-Client Name table

Added some comments

Faked data

Attachment 28314

Code:

---

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

---

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

Quote:

---

Originally Posted by 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.

---

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

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:

Code:

---

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

---

Not tested

Code:

---

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

---

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

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

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

Code:

---

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

---

Quote:

---

Originally Posted by 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

---

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

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

Code:

---

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:

Code:

---

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.

@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

Code:

---

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)

Quote:

---

Originally Posted by p45cal

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

Quote:

---

Originally Posted by Paul_Hossler

...

---

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

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

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

Code:

---

Dim NewCat As Variant
....


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

---

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

Code:

---

Dim NewCat As Variant
....


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

---