Excel VBA 更改数据透视表的数据源

问题描述

好的，我已经筛选了大量帖子，但无法运行我的代码.我正在尝试创建一个对帐报告，我运行得很好.当我尝试获取每个月的报告并将其粘贴到主对帐文件中时，问题就出现了.我的一切工作都很完美，但我一生都无法弄清楚为什么我的数据透视表源不会改变.

Okay, I have sifted through tons of posts and cannot get my code to run. I am trying to create a reconciliation report, which I have running just fine. The problem comes when I am trying to take each month's report and paste it into a master reconciliation file. I have everything working perfectly, except I cannot for the life of me figure out WHY my pivot table source won't change.

我在代码中遇到的错误是:

The error I am getting with the code as is:

运行时错误1004":应用程序定义或对象定义错误

有什么建议吗?:-

    Dim SrcData As String
    Dim PivTbl As PivotTable

    SrcData = ActiveSheet.Name & "!" & Range("$A$1:$H$" & LastRow).Address(ReferenceStyle:=xlR1C1)

    On Error Resume Next
        Set PivTbl = Sheets("Report").PivotTables("ReportPivot")
    On Error GoTo 0
        If PivTbl Is Nothing Then
            'create pivot
        Else
            ActiveWorkbook.PivotTables(PivTbl).ChangePivotCache ActiveWorkbook. _
            PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData, _
            Version:=xlPivotTableVersion15)

            PivTbl.RefreshTable
        End If

推荐答案

既然问题出在这里:

 ActiveWorkbook.PivotTables(PivTbl).ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData, _
        Version:=xlPivotTableVersion15)

我猜你无法获得一个将表本身作为参数传递的数据透视表:.PivotTables(PivTbl)

I guess you cannot get a pivot table passing the table itself as argument: .PivotTables(PivTbl)

PivTbl 已经是你想要的表了.

PivTbl is already the table you want.

PivTbl.ChangePivotCache.......

这篇关于Excel VBA 更改数据透视表的数据源的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！