Pivot Table VBA Correction

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

https://skydrive.live.com/redir?resid=C921C17A2BE69022!104&authkey=!AGiqpb5hajhmv0g

---

Gjergji

Answer 6

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

Answer 7

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

Answer 8

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