+ Reply to Thread
Results 1 to 16 of 16

Unable to set a pivot table to manual update.

  1. #1
    Registered User
    Join Date
    08-16-2013
    Location
    Omaha
    MS-Off Ver
    Excel 2007
    Posts
    10

    Unhappy Unable to set a pivot table to manual update.

    I am using Excel 2007 and my version of visual basic is 6.5.

    I am baffled by the behavior of this code to manipulate one of my pivot tables. I am trying to set all but one of the pivot items in one of the pivot fields to not visible. Because there is a large number of items, I wish to suspend all automatic updates until all items are properly set to visible or non visible.

    ------------------------------------------------------
    Sub SwitchBoards()
    Dim BoardNew As String
    Sheets("Board Parameters").Select
    ' Make sure we get the right value.
    Calculate
    BoardNew = Range("Status!N3")
    ' Update!
    Call UpdatePivotTable("Parameters", "DailyM", "BOARD_KEY", BoardNew)
    .
    .
    .
    Calculate
    End Sub
    ------------------------------------------------------
    Sub UpdatePivotTable(PivSheet As String, PivTable As String, PivField As String, PivItem As String)
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Set pt = ActiveSheet.PivotTables(PivTable)
    With pt
    Set pf = .PivotFields(PivField)
    .PivotCache.Refresh
    End With
    ' Suspend updates till further notice.
    pt.ManualUpdate = True
    '
    MsgBox "Manual update is set to " & pt.ManualUpdate
    '
    ' Set the one we want to visible, if it wasn't already.
    Set pi = pf.PivotItems(PivItem)
    If Not pi.Visible Then pi.Visible = True
    ' Set the rest to invisible.
    For Each pi In pf.PivotItems
    If pi <> PivItem And pi.Visible Then pi.Visible = False
    Next pi
    ' Reset.
    pt.PivotCache.Refresh
    End Sub
    ------------------------------------------------------

    The MsgBox returns with: "Manual update is set to False" right after the instruction to set it to True!! Can anybody educate me as to why manualupdate is not being set the way I want??

    Thanks in advance.
    Attached Images Attached Images

  2. #2
    Registered User
    Join Date
    08-19-2013
    Location
    Sao Paulo, Brasil
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Unable to set a pivot table to manual update.

    According to microsoft your code is right... So it may be that you've set it, and excel hasn't caught up when you msgbox it. Perhaps put a doevents or two in there to see whether it changes.

    Alternatively, if you can get a break point, try and see if it allows you to set / unset via the immediates window... might be something else is blocking it..?

  3. #3
    Registered User
    Join Date
    08-16-2013
    Location
    Omaha
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Unable to set a pivot table to manual update.

    Thanks Ec-niv. I did try it out in the immediate window, but ? pt.ManualUpdate still yields "False" right after the MsgBox prompt.

  4. #4
    Registered User
    Join Date
    08-16-2013
    Location
    Omaha
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Unable to set a pivot table to manual update.

    forgot to include this: I also set it to True in the immediate window, with no success. That setting just doesn't want to get set to True. I have no idea what is blocking it.

  5. #5
    Registered User
    Join Date
    08-19-2013
    Location
    Sao Paulo, Brasil
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Unable to set a pivot table to manual update.

    Ok. Still yields false...

    Did you try setting it manually through the immediates window. Does the status change then?
    Have you frozen all calculations on the book? Might be that the book needs a manual calculation to see the change?

  6. #6
    Registered User
    Join Date
    08-16-2013
    Location
    Omaha
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Unable to set a pivot table to manual update.

    All calculations are frozen as well. And setting it to True in the immediate window has no effect either. Perhaps something is set elsewhere that is blocking it.

  7. #7
    Registered User
    Join Date
    08-19-2013
    Location
    Sao Paulo, Brasil
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Unable to set a pivot table to manual update.

    I can confirm that it doesn't seem to work setting on a pivot table I have here in the immediates window - but it doesn't error neither... which is weird

    One youtube vid shows it working as you'd want it. So I can only think that something is blocking the change...

    Can't see why theirs works and this/mine doesn't - curious now...

  8. #8
    Registered User
    Join Date
    08-19-2013
    Location
    Sao Paulo, Brasil
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Unable to set a pivot table to manual update.

    Found this - but it doesn't seem to help... May be there are permissions on the workbook interferring?
    link here

  9. #9
    Registered User
    Join Date
    08-16-2013
    Location
    Omaha
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Unable to set a pivot table to manual update.

    Yeah, I came across that "Defer Layout Update" before and didn't think it would work.

    This is really vexing.

  10. #10
    Registered User
    Join Date
    08-19-2013
    Location
    Sao Paulo, Brasil
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Unable to set a pivot table to manual update.

    The manual updates is the defer layout... Just turns it off...

    But I still don't see why the code doesn't work, and more importantly, doesn't error.

    Going to have a look now and try some things.

  11. #11
    Registered User
    Join Date
    08-16-2013
    Location
    Omaha
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Unable to set a pivot table to manual update.

    This problem is still unsolved. Can anybody help me?

  12. #12
    Registered User
    Join Date
    04-16-2014
    Location
    Oregon
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Unable to set a pivot table to manual update.

    Hi,

    I don't have a solution, but I have a lead that might be helpful to you.

    I run some VBA code that turns on and off manualupdate for each pivot table in each worksheet.

    It successfully 'refreshes' some of the pivot tables, but not others (for my purpose I'm trying to get changes to my PowerPivot data model)

    The only difference I have found between the ones that don't refresh and the ones that do refresh, is the ones with no data refresh and the ones with data do not refresh. I haven't tested this theory yet, but maybe tomorrow.

    Let me know please, if you have found something since you posted this. Thanks!

  13. #13
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Unable to set a pivot table to manual update.

    Do you change the ActiveSheet anywhere during your code?

    It looks like you end the With..End With block a little early in the code.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  14. #14
    Registered User
    Join Date
    09-10-2014
    Location
    Boston, MA
    MS-Off Ver
    2010
    Posts
    2

    Re: Unable to set a pivot table to manual update.

    I have the same issue as panicpgh. I'm using Excel 2010. I have a pivot table and a slicer. When I use the slicer to filter on a pivot item in the Excel workbook, the pivot table is very fast. The problem is that there are 2000+ slicer items, and it's difficult for users to scroll through the slicer to find the item(s) that they want. So I implemented a combo-box search and iterate over the slicer items in VBA code. For the desired slicer item, we set the Selected property to TRUE. All other slicer items Selected property is set to FALSE.

    The VBA method is so much slower than clicking on an item in the slicer using the mouse. The reason is that the pivot table is updating for each individual slicer item set to slicerItem.Selcted = FALSE. Before iterating, I set the application.ScreenUpdating = false; application.EnableEvents = false; and application.Calculation = Manual. I also set PivotTable.ManualUpdate = true; All of this helps with performance, but it is still so much slower than using the slicer via the UI. I believe the reason is because the PivotTable.ManualUpdate = true does not work. Every time I query the manual update property, it is FALSE.

    Thanks.

  15. #15
    Registered User
    Join Date
    04-16-2014
    Location
    Oregon
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Unable to set a pivot table to manual update.

    Here's an idea, but it may not apply to what you need.

    We do something similar where I'm at. I have the users put their input into an excel table that is connected to the PowerPivot data model. Then this input is available in the model, at which point on each record, I can say, for example that why they entered is found somewhere in another field... True or False in this new field. After that, back in the spreadsheet, if True is selected on this new slicer only the items in the other slicer that are true will show up.

  16. #16
    Registered User
    Join Date
    09-10-2014
    Location
    Boston, MA
    MS-Off Ver
    2010
    Posts
    2

    Re: Unable to set a pivot table to manual update.

    Thanks DjTall, that is a good idea and sounds like it would work. I also was able to filter the table by using "PivotField -> PivotFilters -> Add" with the users selection from the combo-box. In this case, setting "PivotTable.ManualUpdate = true" beforehand is honored by the PivotTable and so the filtering is very fast. Too bad the same doesn't work with the slicer, because it would be nice have the pivot table and slicer in synch. But for now it will have to do.

    Thanks again for your help.

+ 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. How do i add a manual field into a pivot table
    By kenadams378 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-21-2012, 08:52 AM
  2. Replies: 0
    Last Post: 10-27-2011, 11:29 AM
  3. Macro Pivot Table vs Manual Pivot Table - different results
    By vodka in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-03-2008, 04:43 PM
  4. Manual Cross Tabulation - Pivot Table Question
    By ktfish14 in forum Excel General
    Replies: 1
    Last Post: 03-05-2008, 06:50 AM
  5. Excel Pivot Table Manual Sorting
    By mosso in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 06-11-2007, 10:30 AM

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