Results 1 to 8 of 8

Thread: Updating multiple pivot tables at same time.

  1. #1

    Talking Updating multiple pivot tables at same time.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm hoping you can help me. :-) I've been trying to figure out what I need to change in the code, but here's the situation - the field I need to change, "Wk Ending" is a column header. When I update it while I have this code, it loses all filters in all the pivot tables on that sheet (I have several filters on each pivot table, with the common filter being the Wk Ending field) Can you help me? Again, this is a column header that is a date, and when I update it, it is usually with me using a date filter similar to After 5/13/19.Hoping you can help. I feel really lost. :-) Thank you!

    Code:
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    On Error Resume Next
    Dim wsMain As Worksheet
    Dim ws As Worksheet
    Dim ptMain As PivotTable
    Dim pt As PivotTable
    Dim pfMain As PivotField
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim bMI As Boolean
    On Error Resume Next
    Set wsMain = ActiveSheet
    Set ptMain = Target
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    'change only Region field for all pivot tables on active sheet
    Set pfMain = ptMain.PivotFields("Wk Ending")
    bMI = pfMain.EnableMultiplePageItems
    For Each pt In wsMain.PivotTables
    If pt ptMain Then
    pt.ManualUpdate = True
    Set pf = pt.PivotFields("Wk Ending")
    bMI = pfMain.EnableMultiplePageItems
    With pf
    .ClearAllFilters
    Select Case bMI
    Case False
    .CurrentPage = pfMain.CurrentPage.Value
    Case True
    .CurrentPage = "(All)"
    For Each pi In pfMain.PivotItems
    .PivotItems(pi.Name).Visible = pi.Visible
    Next pi
    .EnableMultiplePageItems = bMI
    End Select
    End With
    bMI = False
    Set pf = Nothing
    pt.ManualUpdate = False
    End If
    Next pt
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End Sub
    Last edited by NBVC; 2013-08-14 at 01:03 PM.

  2. #2
    Nobody can run your code without the underlying source data and spreadsheet.
    Upload.

  3. #3
    oh! Ok. I will upload the file shortly. Thank you.

  4. #4
    How do I upload the file? I am not able to do attachments yet it seems. :-(

  5. #5
    Go to any File Sharing site, like http://www.mediafire.com/ and play with it.

  6. #6
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Hi Samait. Sorry, I forgot that you wouldn't be able to upload a document here when I directed you to this forum via my comment at the Contextures blog. Another solution for uploading a file is to post a link via Dropbox. Failing that, send it to me at weir.jeff@gmail.com and I'll upload it for you, as well as take a look.

  7. #7
    thank you. I'll upload shortly. My newest granddaughter was just born, so i've been out of the office. :-)

  8. #8
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    I had a similar problem a while back, if I recall the solution was to write all filters to variables then reset and reload. Will try to dig up code if I have time.

Posting Permissions

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