locked
Pivot Table VBA Correction RRS feed

  • Question

  • Hello So I found this amazing VBA for pivottables but I need to change something on the VBA to make it work for me. I found the VBA on Youtube called "Change all pivot tables when one changes".
    This is the VBA code it self. So what it does it changes all the pivottables when I change the information on one of them. But I want to make it so it only changes the categories I change not everything. To get a better understanding of the VBA code and what it does go to the site. Please help I really need this. Thank You.



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

    For Each pfMain In ptMain.PageFields
    bMI = pfMain.EnableMultiplePageItems
    For Each ws In ThisWorkbook.Worksheets
    For Each pt In ws.PivotTables
    If ws.Name & "_" & pt <> wsMain.Name & "_" & ptMain Then
    pt.ManualUpdate = True
    Set pf = pt.PivotFields(pfMain.Name)
    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
    Next ws
    Next pfMain

    Application.EnableEvents = True
    Application.ScreenUpdating = True

    End Sub
    Friday, October 5, 2012 3:51 PM

All replies

  • Hi GjergjiPro,

    Thank you for posting in the MSDN Forum.

    I've downloaded the Excel Workbook from the site, however I've not quite sure about

    But I want to make it so it only changes the categories I change not everything.

    Could you please provide more detailed description on what you're going to do? I look forward to your reply.

    Best regards,
    Quist


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us

    Monday, October 8, 2012 7:53 AM
  • http://www.youtube.com/watch?v=3CGLi7t1lDE

    Look at this video. The VBA helps change pivot table much quicker. So I took the information from the video and made a small example. So I want the pivot tables to change when I change one, so when I change the year for one of the pivot tables all the years will change too for the other pivot table, which this vba does do. But my problem is that when I do that I also have other categories for example the region which I want it do say the way I put it, but when I do the change of the year or the month the vba also changes the region to be the same as the one pivot table I changed the year in or month.

    Thank you for helping me!

    P.S. I don't know how to attach a file so I hope this will do.


    Gjergji

    Monday, October 8, 2012 10:49 PM
  • Please help I really need this!

    Hello so I found this amazing VBA for pivot tables but I need to change something on the VBA to make it work for me. I found the VBA on Youtube [url]http://www.youtube.com/watch?v=3CGLi7t1lDE[/url].

    This is the VBA code it self. So what it does it changes all the pivot tables when I change the information on one of them. But I want to make it so it only changes the categories I change. Right now if I change for example the date in one it will change it in all of them, but lets say I have a category of people and each table I have set a different person if I change the date in one of the pivot tables the person will also change in every pivot table to be the same as in the one I changed the date in. To get a better understanding of the VBA code and what it does go to the site. Thank You!

    Option Explicit

    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

    For Each pfMain In ptMain.PageFields

    bMI = pfMain.EnableMultiplePageItems

    For Each ws In ThisWorkbook.Worksheets

    For Each pt In ws.PivotTables

    If ws.Name & "_" & pt <> wsMain.Name & "_" & ptMain Then

    pt.ManualUpdate = True

    Set pf = pt.PivotFields(pfMain.Name)

    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

    Next ws

    Next pfMain

    Application.EnableEvents = True

    Application.ScreenUpdating = True

    End Sub



    Gjergji

    • Merged by Quist Zhang Thursday, October 11, 2012 2:44 AM Duplicate
    Wednesday, October 10, 2012 9:09 PM
  • Hi GjergjiPro,

    Sorry to reply to you late.

    You can login SkyDrive using your forum ID and Password, then upload your workbook. After this, you can make it accessible by sharing it and then post the link you've got.

    I look forward to your reply.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, October 11, 2012 2:54 AM
  • https://skydrive.live.com/redir?resid=C921C17A2BE69022!104&authkey=!AGiqpb5hajhmv0g

    Gjergji

    Thursday, October 11, 2012 5:08 AM
  • Hi GjergjiPro,

    Thank you for your last reply.

    If I understood you correctly, the below might be what you want.

    • For example, change the month in Other Pivots sheet into 4.
    • Then the month field in Sales Pivot sheet will change into 4 accordingly while the other fields stay the same as before(Year: 2007 Region: Quebec).
    • Finally, the result appeared in value field ought to be data that filtered by the standard Year:2007 Month:4 Region:Quebec.

    Am I right?

    However according to my research, I think your goal is hard to achieve. For now, I could not find a way to detect exactly which pivot cell has been changed in Other Pivots sheet by code, therefore cannot figure out it's counterpart in Sales Pivot.

    I'll inform you if there's any breakthrough and you are welcomed to make supplements.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us


    • Edited by Quist Zhang Thursday, October 11, 2012 10:46 AM
    Thursday, October 11, 2012 9:54 AM
  • You are correct that's what I'm looking for. So there is no way to make a small change to the code that I provided to get the result I'm looking for?

    Gjergji

    Thursday, October 11, 2012 5:44 PM
  • Hi GjergjiPro,

    Yes. I think you'll need to find other ways to achieve your goal.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us

    • Edited by Quist Zhang Thursday, October 18, 2012 10:01 AM
    Thursday, October 18, 2012 10:01 AM