引用数据透视表，命名为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.

我的任务是重新组织一个非常凌乱的excel文件，其中有几十个数据透视表。重组的一部分将涉及移动数据透视表，既包括在同一张表上，也包括在新表上，以便为它们的增长创造空间。然而，使用getpivotdata()函数的pivot表引用被硬编码到pivot表的位置。

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

我一直在尝试通过使用自定义函数接受pivot表的名称并返回它的位置来解决这个问题，以便在getpivotdata()函数中使用，以便在不引用表破坏的情况下移动pivot表。我尝试返回位置的字符串版本和指定位置的range对象，但这两个都给了我引用错误。到目前为止，我最成功的函数返回了一个数据透视表对象，虽然它没有给我一个引用错误，但它只返回0作为数据，不管我尝试什么

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 个解决方案

#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