excel - 在预定义的位置创建数据透视表

Question

我正在尝试创建一个数据透视表并将其放置在预定义的位置(不是新工作表)。

每次运行宏之前，都会删除数据透视表以及预定义的工作表。

我注意到，当您手动创建表时，表名每次都会增加一个(PivotTable2，PivotTable3 ...)，我认为这是我的代码失败的地方。

我在此行收到运行时错误 5、无效的过程调用或参数:

ActiveWorkbook.PivotCaches.Create

我确实检查了 this线程，它说您可以完全删除表名参数，或重命名它 - 但是我仍然得到错误。

我的代码:

Sub CreatePivot()'
' CreatePivot Macro
'

' Set data as table
    Sheets("Filtered Flags").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$G$16000"), , xlYes).Name _
        = "Table1"

' Create worksheet for pivot output
    Sheets.Add Before:=ActiveSheet
    ActiveSheet.Name = "Flag Pivot"

'Create Pivot Table
    Sheets("Filtered Flags").Select
    Range("Table1[[#Headers],[Order '#]]").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Table1", Version:=6).CreatePivotTable TableDestination:="Flag Pivot!R3C1" _
        , TableName:="PivotTable5", DefaultVersion:=6
    Sheets("Flag Pivot").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Material #")
        .Orientation = xlRowField
        .Position = 1
    End With

End Sub

Answer 1

所以这里有一些事情要做。入门的基础之一(以及理解为什么我的下面的一些示例是构建的)是了解 avoiding the use of Select and Activate 。 .另外，always using Option Explicit 将帮助您回避命名和使用变量的许多问题。

Option Explicit

Public Sub CreatePivot()
    '--- establish some basic objects to use...
    Dim flagsWS As Worksheet
    Set flagsWS = ThisWorkbook.Sheets("Filtered Flags")

    '--- now, define the range of cells that contain the data
    '    and create the pivot cache
    With flagsWS
        Dim lastRow As Long
        Dim lastCol As Long
        Dim dataArea As Range
        Dim dataAreaString As String
        lastRow = .Cells(.Cells.rows.count, 1).End(xlUp).Row
        lastCol = .Cells(1, .Cells.Columns.count).End(xlToLeft).Column
        Set dataArea = .Range(.Cells(1, 1), .Cells(lastRow, lastCol))
        dataAreaString = .Name & "!" & dataArea.Address

        Dim pCache As PivotCache
        Set pCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                                     SourceData:=dataAreaString)
    End With

    '--- create or clear the sheet for our new pivot
    Dim pivotWS As Worksheet
    On Error Resume Next
    Set pivotWS = ThisWorkbook.Sheets("Flag Pivot")
    On Error GoTo 0
    If Not pivotWS Is Nothing Then
        '--- delete everything on the worksheet so we're starting clean
        pivotWS.Cells.Clear
    Else
        Set pivotWS = ThisWorkbook.Sheets.Add
        pivotWS.Name = "Flag Pivot"
    End If

    '--- finally create the pivot table
    Dim flagPT As PivotTable
    Set flagPT = pivotWS.PivotTables.Add(PivotCache:=pCache, _
                                              TableDestination:=pivotWS.Range("A4"), _
                                              TableName:="AnnasPivotTable")

End Sub