Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 30

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

  1. #1
    VBAX Regular
    Joined
    Apr 2021
    Posts
    40
    Location

    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)


    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?
    Last edited by SamT; 04-15-2021 at 11:42 AM.

  2. #2
    VBAX Regular
    Joined
    Apr 2021
    Posts
    40
    Location
    can anyone help please?

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,540
    Location
    Dim Field As PageField
    Set Field = pt.PageFields("TEBUD")
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,739
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    VBAX Regular
    Joined
    Apr 2021
    Posts
    40
    Location
    Quote Originally Posted by SamT View Post
    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

  6. #6
    VBAX Regular
    Joined
    Apr 2021
    Posts
    40
    Location
    Quote Originally Posted by Paul_Hossler View Post
    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

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

  7. #7
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,324
    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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #8
    VBAX Regular
    Joined
    Apr 2021
    Posts
    40
    Location
    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

  9. #9
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,739
    Location
    Capture.JPG

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

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  10. #10
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,324
    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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  11. #11
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,739
    Location
    Quote Originally Posted by p45cal View Post
    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 ...????


    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


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  12. #12
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,324
    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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  13. #13
    VBAX Regular
    Joined
    Apr 2021
    Posts
    40
    Location
    Thanks for your assistance guys. For some god damn reason it still isn't working for me

    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:

    file2.jpg

    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
    Last edited by Ray707; 04-16-2021 at 02:27 AM.

  14. #14
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,324
    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.
    Last edited by p45cal; 04-16-2021 at 05:04 AM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  15. #15
    VBAX Regular
    Joined
    Apr 2021
    Posts
    40
    Location
    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!

  16. #16
    VBAX Regular
    Joined
    Apr 2021
    Posts
    40
    Location
    p.s. it says I've given out too much reputation- Paul I'll give it to you when I'm recharged

  17. #17
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,739
    Location
    Added a little more error checking

    Used a dummy Number-Client Name table

    Added some comments

    Faked data

    Capture.JPG



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

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  18. #18
    VBAX Regular
    Joined
    Apr 2021
    Posts
    40
    Location
    Thanks. I used that to create a pop up message for when there is no data

  19. #19
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,739
    Location
    Quote Originally Posted by p45cal View Post
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  20. #20
    VBAX Regular
    Joined
    Apr 2021
    Posts
    40
    Location
    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
    Last edited by Ray707; 04-21-2021 at 06:44 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •