View Full Version : [SOLVED:] Pivot Tables: error in 'Field.CurrentPage = NewCat'
Ray707
04-14-2021, 09:01 AM
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
04-15-2021, 12:49 AM
can anyone help please? :(
Dim Field As PageField
Set Field = pt.PageFields("TEBUD")
Paul_Hossler
04-15-2021, 12:03 PM
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
04-15-2021, 01:27 PM
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
04-15-2021, 01:41 PM
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
04-15-2021, 02:37 PM
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
04-15-2021, 03:09 PM
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
04-15-2021, 03:50 PM
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
04-15-2021, 05:41 PM
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
04-15-2021, 05:51 PM
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
04-16-2021, 12:04 AM
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
04-16-2021, 02:15 AM
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
04-16-2021, 02:59 AM
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
04-16-2021, 03:58 AM
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
04-16-2021, 03:59 AM
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
04-16-2021, 06:45 AM
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
04-16-2021, 07:46 AM
Thanks. I used that to create a pop up message for when there is no data :cool:
Paul_Hossler
04-16-2021, 08:17 AM
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
04-21-2021, 06:12 AM
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
04-21-2021, 06:26 AM
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
04-21-2021, 06:53 AM
Hi Paul, thanks for the response. I tried that but unfortunately only pivot table 1 updates, pivot table 7 doesn't update :(
p45cal
04-21-2021, 07:25 AM
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
04-21-2021, 07:28 AM
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
04-21-2021, 07:34 AM
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
04-21-2021, 07:49 AM
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
04-21-2021, 08:12 AM
@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
04-21-2021, 08:18 AM
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
04-21-2021, 09:32 AM
...
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
04-22-2021, 01:41 AM
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
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
Dim NewCat As Variant
....
Worksheets("Client").PivotTables("PivotTable7").PivotFields("TEBUD").ClearAllFilters
Worksheets("Client").PivotTables("PivotTable7").PivotFields("TEBUD").CurrentPage = NewCat
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.