记录宏的数据透视表VBA错误 [英] Pivot Table VBA Error from Recorded Macro

问题描述

以下是我对代码的更新。 TableX是我的数据透视表将使用的引用表。在将shtReport变量声明为工作表并进一步命名变量之后，将出现错误对象变量或使用块变量未设置。

Below is my update to the code. TableX is the referenced table that my pivot tables will use. After declaring the 'shtReport' variable as a Worksheet and further naming the variable, the error 'Object variable or With block variable not set' occurs.

Option Explicit

    Sub SutoPivot()

    Dim PvtTbl                          As PivotTable
    Dim PvtCache                        As PivotCache
    Dim shtReport                       As Worksheet

    ' set the Pivot Cache  (NOT SURE what is "TableX" ?)
    Set PvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="TableX", Version:=xlPivotTableVersion14)
    Set shtReport = Worksheets("Invoice Data")

    ' add this line in case the Pivot table doesn't exit >> first time running this Macro
    On Error Resume Next
    Set PvtTbl = Worksheets("Invoice Data").PivotTables("PivotTable1a")

    On Error GoTo 0
    If PvtTbl Is Nothing Then
        ' create a new Pivot Table in "Invoice Data" sheet, start from Cell Y1
        Set PvtTbl = shtReport.PivotTables.Add(PivotCache:=PvtCache, TableDestination:=Worksheets("Invoice Data").Range("Y1"), _ TableName:="PivotTable1a")

        With PvtTbl.PivotFields("Sales Director")
            .Orientation = xlRowField
            .Position = 1
        End With

        With PvtTbl.PivotFields("Manager")
            .Orientation = xlRowField
            .Position = 2
        End With

        With PvtTbl.PivotFields("Owner")
            .Orientation = xlRowField
            .Position = 3
        End With

        With PvtTbl.PivotFields("Account Name")
            .Orientation = xlRowField
            .Position = 4
        End With

        With PvtTbl.PivotFields("Business Name")
            .Orientation = xlRowField
            .Position = 5
        End With

        With PvtTbl.PivotFields("Annual Aggregate Volume")
            .Orientation = xlValuesField
            .Position = 1
        End With

        PvtTbl.AddDataField PvtTbl.PivotFields("Annual Aggregate Volume"),
            "Sum of Annual Aggregate Volume", xlSum

        With PvtTbl.PivotFields("Sum of Annual Aggregate Volume")
            .NumberFormat = "#,##0"
        End With

    Else
        ' just refresh the Pivot cache with the updated Range
        PvtTbl.ChangePivotCache PvtCache
        PvtTbl.RefreshTable
    End If

    End Sub

推荐答案

下面的代码将让您开始自动化cre VBA中数据透视表的更新和更新：

The code below will get you started on automating the creation and updating of PivotTables in VBA:

Option Explicit

Sub SutoPivot()

Dim PvtTbl                          As PivotTable
Dim PvtCache                        As PivotCache

' set the Pivot Cache  (NOT SURE what is "TableX" ?)
Set PvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="TableX", Version:=xlPivotTableVersion14)

' add this line in case the Pivot table doesn't exit >> first time running this Macro
On Error Resume Next
Set PvtTbl = Worksheets("Invoice Data").PivotTables("PivotTable1a")

On Error GoTo 0
If PvtTbl Is Nothing Then
    ' create a new Pivot Table in "Invoice Data" sheet, start from Cell Y1
    Set PvtTbl = shtReport.PivotTables.Add(PivotCache:=PvtCache, TableDestination:=Worksheets("Invoice Data").Range("Y1"), TableName:="PivotTable1a")

    With PvtTbl.PivotFields("Director")
        .Orientation = xlRowField
        .Position = 1
    End With

    With PvtTbl.PivotFields("Manager")
        .Orientation = xlRowField
        .Position = 2
    End With

    With PvtTbl.PivotFields("Owner")
        .Orientation = xlRowField
        .Position = 3
    End With

    With PvtTbl.PivotFields("Account Name")
        .Orientation = xlRowField
        .Position = 4
    End With

    With PvtTbl.PivotFields("Business Name")
        .Orientation = xlRowField
        .Position = 5
    End With

    PvtTbl.AddDataField PvtTbl.PivotFields("Annual Aggregate Volume"), _
        "Sum of Annual Aggregate Volume", xlSum

    With PvtTbl.PivotFields("Sum of Annual Aggregate Volume")
        .NumberFormat = "#,##0"
    End With

Else
    ' just refresh the Pivot cache with the updated Range  
    PvtTbl.ChangePivotCache PvtCache
    PvtTbl.RefreshTable
End If

End Sub

这篇关于记录宏的数据透视表VBA错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！