Results 1 to 2 of 2

Worksheet Change ByVal Target Code Update

Threaded View

  1. #1
    Registered User
    Join Date
    02-13-2008
    Posts
    4

    Worksheet Change ByVal Target Code Update

    My code below works fine now, but I need help adjusting it. Instead of just updating PivotTable2, I also want to make the exact same updates to PivotTable6 (located on the same sheet). I tried a few things now, but no luck yet. Any help would be greatly appreciated!!



    Private Sub Worksheet_Change(ByVal Target As Range)
       
       On Error GoTo ErrorHandler
       
        Dim PvtTable As PivotTable, PvtItem As PivotItem
       
        If (Intersect(Target, Range("B1")) Is Nothing) Then
            Exit Sub
        End If
       
        'PivotTable to update
        Set PvtTable = ThisWorkbook.Worksheets("Summary").PivotTables("PivotTable2")
       
        'Clear all filters on the Job Number Field
        PvtTable.PivotFields("Job Number").ClearAllFilters
       
        'Turn off Application calculation and screen updating
        Application.Calculation = xlCalculationManual
        Application.ScreenUpdating = False
        
        'Stop the PivotCache from recalculating until the selection change has been made
        PvtTable.ManualUpdate = True
       
        For Each PvtItem In PvtTable.PivotFields("Job Number").PivotItems
            'If the current PivotItem caption isn't the same as the selection in cell B1
            'Then set the Item to Visible=True, otherwise Visible=False and hide
            PvtItem.Visible = (PvtItem.Caption = CStr(Range("B1").Value))
        Next
       
    ErrorHandler:
        
        'Turn on Application calculation and screen updating
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
        'Turn the PivotCache calculation on again
        PvtTable.ManualUpdate = False
        
        
    End Sub
    Last edited by AliGW; 10-28-2022 at 07:36 AM. Reason: Code tags added.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Combine Private Sub Worksheet_Change(ByVal Target As Range) VBA Code on same worksheet
    By mark_luke in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-27-2022, 04:48 PM
  2. [SOLVED] ByVal Target not Working with Cube Formula Update?
    By ptmuldoon in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-06-2019, 01:42 PM
  3. VBA - WorkSheet Change ByVal Target As Range and DDE link
    By from water in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-24-2013, 11:13 AM
  4. [SOLVED] Change(ByVal Target As Range) does not work when Target value changes
    By LeonvL in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 01-07-2013, 06:59 PM
  5. [SOLVED] Worksheet_Change(ByVal Target As Range) - set to only update when 3 target cells changed?
    By trillium in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-07-2012, 06:40 PM
  6. Worksheet_Change(ByVal Target As Range) update horizontally.
    By Bhaveen52 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-10-2010, 01:11 PM
  7. 2 target cells for "Change(ByVal..." worksheet macro
    By timmtamm in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-12-2009, 05:12 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