Reference pivot table by name Excel 2010

问题:

I've been tasked with reorganizing an extremely messy excel file with a few dozen pivot tables. Part of the reorganization will involve moving the pivot tables around, both on the same sheet and onto new sheets to create room for them to grow over time. However, pivot table references using the getpivotdata() function are hardcoded to the location of the pivot table.

I've been trying to get around this by using a custom function to accept the name of the pivot table and return its location, for use within the getpivotdata() function to allow me to move the pivot tables around without references to the table breaking. I tried returning a string version of the location and a range object specifying the location but both of those give me reference errors. My most successful function so far has returned a pivottable object, and while it doesn't give me a reference error, it only returns 0 as the data no matter what I try

Here's the current code:

Public Function pivotNameToLoc(PTName As String) As PivotTable
Dim WS As Worksheet, PT As PivotTable

For Each WS In ActiveWorkbook.Worksheets
    For Each PT In WS.PivotTables
        If PT.Name = PTName Then
            pivotNameToLoc = PivotTable
            Exit Function
        End If
    Next PT
Next WS
End Function

Here's how the function is being called in cell formulas

=GETPIVOTDATA("Data field",pivotNameToLoc("Name of table"),"other","fields")

回答1:

You need to return a range:

Public Function pivotNameToLoc(PTName As String) As Range
Dim WS As Worksheet, PT As PivotTable

For Each WS In ActiveWorkbook.Worksheets
    For Each PT In WS.PivotTables
        If PT.Name = PTName Then
            Set pivotNameToLoc = PT.TableRange1.Cells(1)
            Exit Function
        End If
    Next PT
Next WS
End Function