+ Reply to Thread
Results 1 to 5 of 5

error setting pivot field value

  1. #1
    Forum Contributor
    Join Date
    01-08-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    100

    error setting pivot field value

    I have a workbook in which users make a selection from a dropdown. This selection activates an event procedure which makes changes to several pivot tables based on the user selection. However, for some reason, when I try to establish which pivotfields to be changed my procedure fails.

    Private Sub Group_Find_Top10()

    Dim pt20, pt22, pt24, pt25, pt26, pt27 As PivotTable
    Dim Fld1, Fld2, Fld3 As PivotField
    Dim shtChrt, shtTbls As Worksheet

    Set pt20 = Worksheets("Tables").PivotTables("PivotTable20")
    Set pt22 = Worksheets("Tables").PivotTables("PivotTable22")
    Set pt24 = Worksheets("Tables").PivotTables("PivotTable24")
    Set pt25 = Worksheets("Tables").PivotTables("PivotTable25")
    Set pt26 = Worksheets("Tables").PivotTables("PivotTable26")
    Set pt27 = Worksheets("Tables").PivotTables("PivotTable27")
    Set Fld1 = pt.PivotFields("FnctnLvl1")
    Set Fld2 = pt.PivotFields("Lvl678")
    Set Fld3 = pt.PivotFields("CaseTypeRU")
    Set shtChrt = ThisWorkbook.Worksheets("Charts")
    Set shtTbls = ThisWorkbook.Worksheets("Tables")

    If shtChrt.Range("D3") = "Function" Then
    With pt25
    Fld3.ClearAllFilters
    Fld3.CurrentPage = "(All)"
    End With

    and so on...

    all of the pivot tables use the same data and all have at least some of the fields listed.
    This process fails at the Set Fld1 = pt.PivotFields("FnctnLvl1")

    I suspect that I need to be more specific with the "pt." but since those fields appear in several of the tables I would have many Set lines for the fields which I would like to avoid.

    Any help would be greatly appreciated.
    Thanks

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: error setting pivot field value

    You can process them in a loop but you need to handle errors if some of the pivots don't have the same fields:
    Please Login or Register  to view this content.
    for example.
    Remember what the dormouse said
    Feed your head

  3. #3
    Forum Contributor
    Join Date
    01-08-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    100

    Re: error setting pivot field value

    romperstomper, Thanks very much for your reply. It makes perfect sense based on the information I originally provided, but, I am going to have to think about how to make it work. As you say, I will have errors when the fields don't exist but also I dont necessarily want to change all the fields even if they do exist. I guess what I am looking for is a way to change only speciic fields in specific tables. I have all the logic mapped out but I cant get the syntax correct.
    for example, All of the tables have Fld1 but only pt22, pt25 and pt27 have Fld3.
    When the user selects "Function" I only want to change Fld3 in pt25, Fld1 in pt26, and Fld1 and Fld3 in pt27.
    and so on... the logic is complicated but what it boils down to is I only want to change some of the pivotfields in some of the pivottables based on the combination of selections made.
    Thanks Again

  4. #4
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: error setting pivot field value

    Unless there's a particular logic, I think you will have to simply code each instruction individually.

  5. #5
    Forum Contributor
    Join Date
    01-08-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    100

    Re: error setting pivot field value

    thanks again. Thats what I did until I find a cleaner method. I just named Fld1 for pt20, Fld120 etc. So, there are 18 Set Fld stmts per Sub. Kinda Brute force and not very pretty but it does work.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 02-13-2014, 04:32 PM
  2. Changing multiple value field setting in Pivot table
    By flarwood in forum Excel General
    Replies: 5
    Last Post: 06-13-2012, 08:33 AM
  3. field setting help in pivot tables
    By aravindhan_31 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-20-2009, 06:11 AM
  4. VBA Issue / Setting Pivot Field Page Default Item
    By AndreLaplume in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-20-2007, 02:38 PM
  5. Setting pivot field using vba
    By h2o in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-30-2006, 05:03 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1