Update Mutiple Pivot Tables on 2 sheets from FieldTable Worksheet Change tweak needed

S

smd747

Board Regular
Joined
Apr 24, 2011
Messages
214
Due to the problem with stacking pivot tables on top of each other I find myself using addition worksheets to create Pivot table reports.

Here is the question. How can I link this "FieldTable" to update multiply worksheets based on the Worksheet Change event?
I have 3 sheets:
  1. Calculator
  2. Command Center
  3. Breakdown

The Calculator sheet has the Worksheet change event which updates the all the Command Center pivot tables from the FieldTable. I want to add the Breakdown sheet also. But have been unsuccessful whenever I add them. Everything is triggered from selecting the date on the Calculator sheet.

Here is the wonderful code from board member JS411, that use over and over, I just need to expand to multiply sheets.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sDV_Address As String
    Dim vFieldTable() As Variant
    Dim i As Long
 
  sDV_Address = "$A$2" 'Cell with date to select filter item.
'    With ActiveSheet
    With Sheets("CommandCenter")
        If Intersect(Target, Range(sDV_Address)) Is Nothing Or _
            Target.Cells.Count > 1 Then Exit Sub
 
        On Error GoTo CleanUp
        Application.EnableEvents = False
       
        vFieldTable = Application.Transpose(Sheets("CommandCenter") _
            .Range("FieldTable"))
 
        For i = LBound(vFieldTable, 2) To UBound(vFieldTable, 2)
            Call Filter_PivotField( _
                pvtField:=.PivotTables(vFieldTable(1, i)) _
                    .PivotFields(vFieldTable(2, i)), _
                    vItems:=Target.Value)
        Next i
    End With
 
CleanUp:
    Application.EnableEvents = True
End Sub

The function that goes with this is:

Code: 
Public Function Single_Page_Filter(pvtField As PivotField, _
        sValue As String) As Boolean
    
    On Error GoTo ErrorHandler
    With pvtField
        .ClearAllFilters
        .CurrentPage = sValue
    End With
    Single_Page_Filter = True
    Exit Function
ErrorHandler:
    Select Case Err.Description
        Case "Application-defined or object-defined error"
            MsgBox sValue & " has no data in " _
                & pvtField.Parent.Name
        Case Else
            MsgBox Err.Number & ": " & Err.Description
    End Select
 
    With pvtField
        .ClearAllFilters
        .CurrentPage = "No Data Found"
    End With
    Single_Page_Filter = False
End Function


CommandCenter


W
X
Y
Z
AA
PivotTable1
PROCESSING_DATE
commandCenter Sheet
PivotTable2
PROCESSING_DATE
commandCenter Sheet
PivotTable3
PROCESSING_DATE
commandCenter Sheet
PivotTable4
PROCESSING_DATE
Breakdown Sheet
PivotTable5
PROCESSING_DATE
Breakdown Sheet

<tbody>
[TD="bgcolor: #CACACA, align: center"]1
[/TD]

[TD="bgcolor: #CACACA, align: center"]2
[/TD]

[TD="bgcolor: #CACACA, align: center"]3
[/TD]

[TD="bgcolor: #CACACA, align: center"]4
[/TD]

[TD="bgcolor: #CACACA, align: center"]5
[/TD]

[TD="bgcolor: #CACACA, align: center"]6
[/TD]

</tbody>

How do I get the The Calculator sheet has the Worksheet change event to update both the commandCenter and the Breakdown sheets piviot table.

Any suggestions

Thanks



Sorry about this distorted genie screenshot, I’m new to using it and can’t get rid of it so its tagging along. A2 is the date that triggers all the pivot table changes.
A
B
C
D
E
F
G
H
I
J
K
Process Date
Time Result based on Number of Pickers

<tbody>
[TD="bgcolor: #CACACA, align: center"]1
[/TD]

[TD="colspan: 2, align: right"]TransactionType
[/TD]

[TD="bgcolor: #CACACA, align: center"]2
[/TD]
[TD="bgcolor: #C0C0C0, align: left"]7/2/2012
[/TD]

[TD="align: center"][/TD]

[TD="bgcolor: #CACACA, align: center"]3
[/TD]

[TD="align: center"]Enter
[/TD]
[TD="align: center"]Enter
[/TD]

[TD="bgcolor: #CACACA, align: center"]4
[/TD]
[TD="bgcolor: #666699"]QueGroup
[/TD]
[TD="bgcolor: #666699, align: center"]Min
Requirement
[/TD]
[TD="bgcolor: #666699, align: center"]Hourly
Required to Meet
[/TD]
[TD="bgcolor: #993366, align: center"]Break
Down of Instructions
[/TD]
[TD="bgcolor: #C0C0C0, align: center"]No
of Pickers assigned
[/TD]
[TD="bgcolor: #FF0000, align: center"]Hourly
Picks Per Team Needed
[/TD]
[TD="bgcolor: #666699, align: center"]Meeting
Min Hours Time Required to Complete
[/TD]
[TD="bgcolor: #666699, align: center"]Department
[/TD]
[TD="bgcolor: #666699, align: center"]Dept
% Breakdown
[/TD]

[TD="bgcolor: #CACACA, align: center"]5
[/TD]
[TD="bgcolor: #CCCCFF"]Bulk
[/TD]
[TD="bgcolor: #CCCCFF, align: right"]114
[/TD]
[TD="bgcolor: #CCCCFF, align: right"] 19
[/TD]
[TD="bgcolor: #C0C0C0, align: right"]231
[/TD]
[TD="bgcolor: #C0C0C0, align: right"]2
[/TD]
[TD="bgcolor: #FF0000, align: right"]38
[/TD]
[TD="bgcolor: #CCCCFF, align: center"]6.1
[/TD]
[TD="bgcolor: #CCCCFF"]Bulk
[/TD]
[TD="bgcolor: #CCCCFF, align: right"]9%
[/TD]

[TD="bgcolor: #CACACA, align: center"]6
[/TD]
[TD="bgcolor: #CCCCFF"]Mezzanine
[/TD]
[TD="bgcolor: #CCCCFF, align: right"]326
[/TD]
[TD="bgcolor: #CCCCFF, align: right"] 54
[/TD]
[TD="bgcolor: #C0C0C0, align: right"]1132
[/TD]
[TD="bgcolor: #C0C0C0, align: right"]5
[/TD]
[TD="bgcolor: #FF0000, align: right"]272
[/TD]
[TD="bgcolor: #CCCCFF, align: center"]4.2
[/TD]
[TD="bgcolor: #CCCCFF"]Mezzanine
[/TD]
[TD="bgcolor: #CCCCFF, align: right"]43%
[/TD]

[TD="bgcolor: #CACACA, align: center"]7
[/TD]
[TD="bgcolor: #CCCCFF"]Pallet Rack
[/TD]
[TD="bgcolor: #CCCCFF, align: right"]162
[/TD]
[TD="bgcolor: #CCCCFF, align: right"] 27
[/TD]
[TD="bgcolor: #C0C0C0, align: right"]876
[/TD]
[TD="bgcolor: #C0C0C0, align: right"]5
[/TD]
[TD="bgcolor: #FF0000, align: right"]135
[/TD]
[TD="bgcolor: #CCCCFF, align: center"]6.5
[/TD]
[TD="bgcolor: #CCCCFF"]Pallet Rack
[/TD]
[TD="bgcolor: #CCCCFF, align: right"]34%
[/TD]

[TD="bgcolor: #CACACA, align: center"]8
[/TD]
[TD="bgcolor: #CCCCFF"]Turret
[/TD]
[TD="bgcolor: #CCCCFF, align: right"]140
[/TD]
[TD="bgcolor: #CCCCFF, align: right"] 23
[/TD]
[TD="bgcolor: #C0C0C0, align: right"]136
[/TD]
[TD="bgcolor: #C0C0C0, align: right"]1
[/TD]
[TD="bgcolor: #FF0000, align: right"]23
[/TD]
[TD="bgcolor: #CCCCFF, align: center"]5.8
[/TD]
[TD="bgcolor: #CCCCFF"]Turret
[/TD]
[TD="bgcolor: #CCCCFF, align: right"]5%
[/TD]

[TD="bgcolor: #CACACA, align: center"]9
[/TD]
[TD="bgcolor: #CCCCFF"]Upper Mezzanine
[/TD]
[TD="bgcolor: #CCCCFF, align: right"]120
[/TD]
[TD="bgcolor: #CCCCFF, align: right"] 20
[/TD]
[TD="bgcolor: #C0C0C0, align: right"]101
[/TD]
[TD="bgcolor: #C0C0C0, align: right"]1
[/TD]
[TD="bgcolor: #FF0000, align: right"]20
[/TD]
[TD="bgcolor: #CCCCFF, align: center"]5.1
[/TD]
[TD="bgcolor: #CCCCFF"]Upper Mezzanine
[/TD]
[TD="bgcolor: #CCCCFF, align: right"]4%
[/TD]

[TD="bgcolor: #CACACA, align: center"]10
[/TD]
[TD="bgcolor: #CCCCFF"]Yard
[/TD]
[TD="bgcolor: #CCCCFF, align: right"]326
[/TD]
[TD="bgcolor: #CCCCFF, align: right"] 54
[/TD]
[TD="bgcolor: #C0C0C0, align: right"]123
[/TD]
[TD="bgcolor: #C0C0C0, align: right"]1
[/TD]
[TD="bgcolor: #FF0000, align: right"]54
[/TD]
[TD="bgcolor: #CCCCFF, align: center"]2.3
[/TD]
[TD="bgcolor: #CCCCFF"]Yard
[/TD]
[TD="bgcolor: #CCCCFF, align: right"]5%
[/TD]

[TD="bgcolor: #CACACA, align: center"]11
[/TD]
[TD="bgcolor: #CCCCFF"]Tagged Slash Section
[/TD]
[TD="bgcolor: #CCCCFF, align: right"]80
[/TD]
[TD="bgcolor: #CCCCFF, align: right"] 13
[/TD]
[TD="bgcolor: #C0C0C0, align: right"]10
[/TD]
[TD="bgcolor: #C0C0C0, align: right"]4
[/TD]
[TD="bgcolor: #FF0000, align: right"]53
[/TD]
[TD="bgcolor: #CCCCFF, align: center"]0.2
[/TD]
[TD="bgcolor: #CCCCFF"]Tagged Slash Section
[/TD]
[TD="bgcolor: #CCCCFF, align: right"]0%
[/TD]

[TD="bgcolor: #CACACA, align: center"]12
[/TD]

[TD="bgcolor: #993366, align: right"]2609
[/TD]
[TD="bgcolor: #C0C0C0, align: right"]19
[/TD]
[TD="bgcolor: #FF0000, align: right"]596
[/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #969696"][/TD]
[TD="bgcolor: #FFFF00, align: right"]100%
[/TD]

[TD="bgcolor: #CACACA, align: center"]13
[/TD]

[TD="bgcolor: #CACACA, align: center"]29
[/TD]

</tbody>


Excel
tables to the web >> Excel Jeanie
HTML 4
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Hi Stephen,

The code below should work based on defining the named range: FieldTable as W2:Y6 in the screen shot.
Excel Workbook
WXY
1WorksheetPivotTablePivotField
2CommandCenterPivotTable1PROCESSING_DATE
3CommandCenterPivotTable2PROCESSING_DATE
4CommandCenterPivotTable3PROCESSING_DATE
5BreakdownPivotTable4PROCESSING_DATE
6BreakdownPivotTable5PROCESSING_DATE
Sheet


Replace this procedure in the sheet with the date selection in A2...
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sDV_Address As String
    Dim vFieldTable() As Variant
    Dim i As Long
 
    sDV_Address = "$A$2" 'Cell with date to select filter item.

    If Target.Address <> sDV_Address Then Exit Sub

    On Error GoTo CleanUp
    Application.EnableEvents = False
    
    vFieldTable = Application.Transpose(Sheets("CommandCenter") _
        .Range("FieldTable"))

    For i = LBound(vFieldTable, 2) To UBound(vFieldTable, 2)
        Call Single_Page_Filter( _
             Sheets(vFieldTable(1, i)).PivotTables(vFieldTable(2, i)) _
            .PivotFields(vFieldTable(3, i)), Target.Value)
    Next i
 
CleanUp:
    Application.EnableEvents = True
End Sub

Modify this function in your standard code module to read...
Code: 
Public Function Single_Page_Filter(pvtField As PivotField, _
        sValue As String) As Boolean
    
    On Error GoTo ErrorHandler
    With pvtField
        .ClearAllFilters
        .CurrentPage = sValue
    End With
    Single_Page_Filter = True
    Exit Function

ErrorHandler:
    Select Case Err.Description
        Case "Application-defined or object-defined error"
            MsgBox sValue & " has no data in " _
                & pvtField.Parent.Name
        Case Else
            MsgBox Err.Number & ": " & Err.Description
    End Select
 
    With pvtField
        .ClearAllFilters
    End With
    Single_Page_Filter = False
End Function
 
Upvote 0
Jerry made the changes you suggested, works great. I see and understand what you did. I was trying to reference the other worksheet, and my codes keep breaking down given me error codes.</SPAN>

Your solution is less code, and simplifies the code , does exactly what I need it to do. It is quite elegant and amazing. I will reuse over and over again.</SPAN>

Thanks once again for putting me back in the right directions and educating me on my VBA journey.</SPAN>

Thanks You </SPAN>
smd747</SPAN>

Your amazing , thanks for your help and guidance.</SPAN>
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
Change the Pivot Table from another sheet
Replies
2
Views
159
Ongbey
O
J
  • Question Question
Pivot table from 2 tables not "filtering" as expected
Replies
0
Views
253
jorgemarmo
J
trishcollins
  • Question Question
Private Worksheet Pivot table Update is affecting Pivot table on another worksheet which uses same data source
Replies
0
Views
479
trishcollins
trishcollins
C
  • Question Question
VBA Consolidating Multiple Sheets in a Pivot Table
Replies
1
Views
313
c_clark
C
C
  • Question Question
Pivot Table from Power Query - reorder Columns (Excel 2021)
Replies
4
Views
551
craigcurtin
C

Forum statistics

Threads
1,223,923
Messages
6,175,389
Members
452,640
Latest member
steveridge

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top