Referencing Pivot Table Sourced From ActiveSheet?

[Nuke_It_Newport]

Hi everyone-

I am curious if there's any method to reference a pivot table (on a separate sheet) that is sourced from the active sheet without calling it explicitly. The context of this is as follows:

In my active sheet, If Range("C2:E2") value is changed, this value is pasted into the associated PivotTable header. I would like to move this code from the Worksheet_Change modules to the Workbook_SheetChange module, but I would need a way to reference the PivotTable that is sourcing from the active sheet.
Here's a sample of the code I have in the Worksheet_Change modules.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("C2:E2")) Is Nothing Then
    If Len(Target) = 0 Then
        MsgBox "Worksheet title cannot be left blank. Reverting to previous title...", vbOKOnly, vbCritical
        Application.Undo
    Else
        On Error GoTo Err_Handler
        ActiveSheet.Name = Target.Value
        With ThisWorkbook.Sheets("~WKSP 1 PT").PageSetup
            .CenterHeader.Text = Target.Value
        End With
    End If
End If
Exit Sub

Err_Handler:
If Err.Number = 1004 Then
    MsgBox "The sheet could not be renamed. Please check " & vbCrLf & _
    "that you did not use illegal characters."
    Application.Undo
    Resume Next
End If

End Sub

Here's the code I've got in Workbook_SheetChange:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Not Left(Trim(ActiveSheet.Name), 1) = "~" Then
    If Not Intersect(Target, Sh.Range("C2:E2")) Is Nothing Then
        If Len(Target.Text) = 0 Then
            MsgBox "Worksheet title cannot be left blank. Reverting to previous title...", vbOKOnly, vbCritical
            Application.Undo
        Else
            On Error GoTo Err_Handler
            ActiveSheet.Name = Target.Value
            
'            With      ***NEED TO REFERENCE PIVOT TABLE HERE...
'               .CenterHeader.Text = Target.Value
'            End With

        End If
    End If
End If
Exit Sub

Err_Handler:
If Err.Number = 1004 Then
    Debug.Print "Worksheet renaming error. Check for illegal characters."
    MsgBox "The sheet could not be renamed. Please check " & vbCrLf & _
    "that you did not use illegal characters."

    Application.Undo
    Resume Next
End If
Application.EnableEvents = False
    ThisWorkbook.RefreshAll
Application.EnableEvents = True

End Sub

Is it better to leave this code in the Worksheet_Change modules? I'm trying to clean up my code.
Thanks!

 

[RoryA]

Your current code has no mention of pivot tables in it anywhere, so I'm not sure what it is you actually want?

 

[Nuke_It_Newport]

Your current code has no mention of pivot tables in it anywhere, so I'm not sure what it is you actually want?

If you look at the first section of code I pasted, I have the following:

With ThisWorkbook.Sheets("~WKSP 1 PT").PageSetup
    .CenterHeader.Text = Target.Value
End With

What I'm wondering is if I can reference the pivot table connected to the active sheet without calling it by name. Is there a method to replace "With ThisWorkbook.Sheets("~WKSP 1 PT").PageSetup" with "[ActiveSheet].[Associated Pivot Table].PageSetup", if that makes sense. Thanks!

 

[RoryA]

Pivot tables don't have a pagesetup property so that doesn't really make sense per se. Do you want to change the header of the sheet for any pivot table (or pivot tables as there could be multiple) whose source data is on the sheet being changed? If so, you will have to loop through all the sheets and all the pivot tables in turn.

 

[Nuke_It_Newport]

Pivot tables don't have a pagesetup property so that doesn't really make sense per se. Do you want to change the header of the sheet for any pivot table (or pivot tables as there could be multiple) whose source data is on the sheet being changed? If so, you will have to loop through all the sheets and all the pivot tables in turn.

Thanks. Yes, that's what I was looking to do, I couldn't think of how to describe it. I'm printing the pivot table sheets for use in the field. When the end user enters text in a "Title" cell on the source worksheet, I want the header on the pivot table worksheet to match this text. I considered looping through all the sheets and pivot tables each time the target range changes on a sheet, but I think it's better to leave things the way they are, and just change the header on the sheet containing the pivot table when the source worksheet is changed. Thanks for your help!