Results 1 to 13 of 13

Updating multiple pivot tables on one worksheet only

Threaded View

  1. #1
    Registered User
    Join Date
    11-17-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    6

    Updating multiple pivot tables on one worksheet only

    Hi I have the following VBA code which works across every worksheet in my workbook, however I only want it to work on one specific worksheet and don't know how to amend it. Any help greatly appreciated.

    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 ActiveWorkbook.Worksheets
            For Each pt In ws.PivotTables
                If ws.Name & "Sheet2" & pt <> wsMain.Name & "Sheet2" & 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 = "(Sheet2)"
                                        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
    Last edited by Simpson67; 11-17-2016 at 09:37 AM. Reason: RULES

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Amending data
    By rode40 in forum Excel General
    Replies: 1
    Last Post: 01-15-2013, 05:11 PM
  2. [SOLVED] Amending a Key formula
    By pauldaddyadams in forum Excel General
    Replies: 18
    Last Post: 10-06-2012, 04:37 PM
  3. Amending One Field from Another
    By cgolds in forum Excel General
    Replies: 3
    Last Post: 04-17-2012, 11:59 AM
  4. Help with VBA amending
    By sideshow1987 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-26-2011, 07:32 AM
  5. Amending Sub totals
    By AK262007 in forum Excel General
    Replies: 1
    Last Post: 05-01-2009, 10:40 AM
  6. Amending an if statement
    By isolation123 in forum Excel General
    Replies: 27
    Last Post: 03-13-2008, 08:02 AM
  7. Amending code
    By khalid79m in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-26-2007, 03:23 PM
  8. Help with Amending this Code Please
    By [email protected] in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-01-2005, 03:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1