drag & drop Pivot Table question

[TomLyn]

I use multiple pivot tables on same and different worksheets within a workbook. I am able to filter all the pivot reports at the same time using the and "Item" in the report filter. The result is that when one filter is changed all the other pivot tables with the same "Item" filter will change. I would like the same result when I apply the drag and drop manual sort with in the Row Labels. (example: drag the third row of data (in the Row label column) up to the 1st row of data) The result would be all tables would change together when the drag and drop filter method is used.
The Report filters, Row labels and Column labels are identical in each pivot report.

Is this doable?

Thanks you for your time

Tom

 

[p45cal]

I suspect it is, but I doubt it would be straightforward.
Certainly a file of some type from you would be extremely helpful so that (a) others trying to help don't get it wrong and (b) you'll have less code re-writing to do to make someone's answer fit your specific requirements.

 

[David Evans]

I suspect it is, but I doubt it would be straightforward.
Certainly a file of some type from you would be extremely helpful so that (a) others trying to help don't get it wrong and (b) you'll have less code re-writing to do to make someone's answer fit your specific requirements.

Agree @p45cal - it's always easier to comprehend (or to not mis-understand) when you have a file - I suspect you can use slicers for what you're trying to describe (@TomLyn ) .... but it's a bit of a SWAG on my part

 

[TomLyn]

Thanks for the replies and suggestions. See if the attached workbook makes my inquiry a bit clearer. Thanks Again

 

[David Evans]

Thanks for the replies and suggestions. See if the attached workbook makes my inquiry a bit clearer. Thanks Again

OK - I see what you're trying to achieve - I think it can only be done via VBA, but I'm never surprised by the ingenuity of the folks around here! I'll give you a link to a source for information - http://www.globaliconnect.com/excel...use-custom-lists-with-vba&catid=79&Itemid=475

I'm tied up most of today, but if I have an opportunity later I will return to this as I'd like to have a shot at it - it's an interesting exercise ... also, someone else around here may climb on their Excel Steed and save the day ....

 

[p45cal]

see attached, no attempt to make it robust, but it should give you a start.

 

[David Evans]

see attached, no attempt to make it robust, but it should give you a start.

Very Nice, @p45cal ! I need to store this solution somewhere as I can see it being a useful trick. Thanks to @TomLyn for posing the question also ...

 

[TomLyn]

Thanks Guys, It certainly works in the example I provided. I appreciate the help. However I am stumped, I copied your code over to my workbook and the Report Filters work fine, but when I Drag and Drop the Row labels around they do not change in the other sheets. Not sure what I am doing wrong, but I may have to ask for some help on figuring this out

@p45cal - what does the following code reference
On Error Resume Next
Set wsMain = Sheets("Sales Pivot")
Set ptMain = Target

thanks again
TomLyn

 

[p45cal]

what does the following code reference
On Error Resume Next
Set wsMain = Sheets("Sales Pivot")
Set ptMain = Target

All of the above code is your code!
There was no Sales Pivot sheet in your sample workbook so I assumed it was in your actual working file and left it alone.
Set ptMain = Target
means that the object variable ptMain is the pivot table being updated by the user. This is the one the other pivots are made to match by the code.

So does this mean that you got the code from elsewhere and it really needs to be tailored/rewritten for the sample workbook you supplied?

 

[David Evans]

Thanks Guys, It certainly works in the example I provided. I appreciate the help. However I am stumped, I copied your code over to my workbook and the Report Filters work fine, but when I Drag and Drop the Row labels around they do not change in the other sheets. Not sure what I am doing wrong, but I may have to ask for some help on figuring this out

@p45cal - what does the following code reference
On Error Resume Next
Set wsMain = Sheets("Sales Pivot")
Set ptMain = Target

thanks again
TomLyn

POst your "new file" here and we'll get it working pronto for you ---- did you put this snippet on each sheet you want to "emulate" your first sheet?

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
blah Target
End Sub

 

[TomLyn]

Sorry Guys, had a senior moment there. I am good and all is working fine. Thanks again for your assistance.

TomLyn

 

[TomLyn]

Sorry guys another small Obstacle I need help with.

The sample sheet you wrote the code for was awesome and work flawlessly but it was tailored to show the process outcome on a much smaller scale.

The example was much smaller in size and the number of worksheets included in the update process. In the real world my workbook has 37 worksheets all with identical pivot table formats with the exception of the value fields.

Thanks again as the code works great and changes the Report Filter and Row label filters as designed. However, I am growing older watching it loop through all 37 sheets to make the requested updates.

My thought is to modify the VBA so it only runs on open and active worksheets (not hidden worksheets), but I have been unsuccessful in making the code work.

Any suggestions on this or how to speed up the updating process?

BTW I did change the file extension to “xlsb” in hopes it would help the processing speed, but if it did it was negligible.

Thanks again for your time

 

[David Evans]

Have you used the Debug tools to show you where the code is slowing down? I looked at @p45cal code quickly and can see nothing to suggest that it would be slowed down by adding more worksheets - it appears generic and scalable from a quick glance - I suspect that the problem is with something else in your expanded workbook, which the Watch Window will help you identify. However, I will defer to the author's assessment as he knows what he was thinking when he developed it ..
If you can post the offending worksheet, it will make it easier to assist you ...
D

 

[p45cal]

The attached has some 50+ pivot tables on some 40 sheets.
I've managed to get about a 50x speed increase without avoiding updating any pivot tables.
You'll see the code in blah has been moved to the ThisWorkbook code-module in the guise of the Workbook_SheetPivotTableUpdate event handler. This triggers with the updating of any pivot table on any sheet without the need for any code at all in the individual worksheet code-modules (in fact, in your file, there shouldn't be any code which calls blah - I've commented them all out).
In Module 1 I've left (but commented-out) the new blah code.

You need to verify that all pivot tables update properly regardless of which pivot table is manually altered.

 

[TomLyn]

p45cal, this change works like a track star over the previous version and certainly improved response time for the filter changes.

What puzzles me is why did the change from the module and sheets to the thisworksheet function improve the response. Educate me please and thanks so much for your time in figuring this out.

Tom

 

[p45cal]

What
puzzles me is why did the change from the module and sheets to the thisworksheet function improve the response time?

That change didn't make even the slightest difference to the speed, it was just one of the things I tried. It does, however make it easier to add sheets and write code.
I got a 20x speed boost by removing pt.refreshtable after which I got a further 2.5 increase with pt.manualupdate =TRUE/FALSE and a reduction in the number of loops and by testing if an Employee was already in the right position before moving him.

 

[TomLyn]

Wow! impressive, Thanks so much for your help...

 

[jeffreyweir]

Here's some ideas on how you can make this more efficient still.
1. Instead of using code to replicate any changes to filter selections across all the PivotTables, use Slicers to connect identical PivotFields across all the PivotTables. A Slicer will ensure that filter selections are always kept in sync. Then you can ditch most of that code.
2. Check the undo stack before you run the code, so that it only gets triggered when the actual structure changes. See http://dailydoseofexcel.com/archives/2014/07/08/what-caused-that-pivottableupdate-part-two/ on how to do this, or post back here.

 

[TomLyn]

Thanks, I tried the slicer route, but it did not give me the same contents order on the report as the "Drag and Drop" filter method produced. I will look at the you provided. I appreciate your input

 

[jeffreyweir]

You misunderstand me in regards to the slicers. I'm not talking about the part of the code that relates to drag-and-drop, but rather to the part of the code that syncs the filter selection across pivots. i.e. this bit of code:

For Each pfMain In Target.PageFields
        For Each pf In pt.PageFields
          If pf.Name = pfMain.Name Then
            If pfMain.CurrentPage = "(All)" Then
              pf.CurrentPage = "(All)"
              Exit For
            End If
            For Each pi In pf.PivotItems
              If pi.Name = pfMain.CurrentPage Then
                pf.CurrentPage = pi.Name
                Exit For
              End If
            Next pi
          End If
        Next pf
      Next pfMain

That code can be replaced with a slicer. This would speed things up because currently this bit of code runs any time any type of change is made to the PivotTable...even if that change did not change the filter selection.