带有宏的Excel数据透视表

I recorded a macro that generated a very simple pivot table. When I played the macro back I get an error in the PivotTable.

我录制了一个生成非常简单的数据透视表的宏。当我播放宏时,我在数据透视表中出现错误。

I get:

Invalid procedure call or argument

无效的过程调用或参数

So I went back and put single quotes around the SourceData and TableDestination. Now I get a pivot table but only with the total. It should give me the count of all the occurrences of the items in Column A.

所以我回去并在SourceData和TableDestination周围放了单引号。现在我得到一个数据透视表,但只有总数。它应该给我列A中所有项目的计数。

Here's the code

这是代码

 Sub testpivot()
'
' testpivot Macro
'
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "'GF Response Detail R'!R1C1:R65536C1", Version:= _
    xlPivotTableVersion10).CreatePivotTable TableDestination:= _
    "'GF Response Detail R'!R2C10", TableName:="PivotTable1", _
    DefaultVersion:=xlPivotTableVersion10
Sheets("GF Response Detail R").Select
Cells(2, 7).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Region")
    .Orientation = xlRowField
    .Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("Region"), "Count of Region", xlCount
    ActiveWorkbook.ShowPivotTableFieldList = False

End Sub

3 个解决方案

#1

You want to remove the Pivot Table from the worksheet. Before you run the macro.

您想要从工作表中删除数据透视表。在运行宏之前。

#2

The code below will check first if there's a "PivotTable1" in the sheet, and if it does it will delete it.

下面的代码将首先检查工作表中是否有“PivotTable1”,如果有,它将删除它。

Afterwards, it will create a new PivotTable on "GF Response Detail R" sheet, with the updated data in Column "A".

之后,它将在“GF Response Detail R”表上创建一个新的数据透视表,其中更新的数据在“A”列中。

Code

Option Explicit

Sub testpivot()

' testpivot Macro
Dim PivTbl      As PivotTable
Dim PivCache    As PivotCache
Dim DataSht     As Worksheet
Dim lastRow     As Long
Dim SrcRng      As Range
Dim SrcData     As String

' set the Pivot Data
Set DataSht = Worksheets("GF Response Detail R")    
With DataSht
    lastRow = .Range("A" & .Rows.Count).End(xlUp).Row '<-- get last row in Column A

    Set SrcRng = .Range("A1:A" & lastRow) '<-- set dynamic Pivot Range
    SrcData = SrcRng.Address(True, True, xlA1, xlExternal) '<-- get the Range Address, including sheet's name
End With    

'-- first check if there's a "PivotTable1" in sheet >> if Yes, Delete it
For Each PivTbl In DataSht.PivotTables
    If PivTbl.Name = "PivotTable1" Then
        DataSht.Range(PivTbl.TableRange2.Address).Delete Shift:=xlUp
        Exit For
    End If
Next PivTbl

' set the Pivot Cache
'Set PivCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData)

' Option 2: set the Pivot Cache
Set PivCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcRng)

' Option 3: set the Pivot Cache
Set PivCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData, Version:=xlPivotTableVersion15)

' create a new Pivot Table in "EXP Pivot" sheet, start from Cell A1
Set PivTbl = DataSht.PivotTables.Add(PivotCache:=PivCache, TableDestination:=DataSht.Range("J2"), TableName:="PivotTable1")

With PivTbl
    With .PivotFields("Region")
        .Orientation = xlRowField
        .Position = 1
    End With

    .AddDataField .PivotFields("Region"), "Count of Region", xlCount
    ActiveWorkbook.ShowPivotTableFieldList = False
End With

End Sub

#3

Here try this. It is a slight variation of what @Shai Rado was suggesting.

试试吧。这是@Shai Rado所暗示的略有不同。

Sub RegionMacro()

Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long

'define the sheet, last row, and pivot table range
Set DSheet = Worksheets("GF Response Detail R")
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set PRange = DSheet.Range("A1:A" & LastRow)

'get address of range
sData = PRange.Address(False, True)

'check to see if the table already exist and delete it if it does
For Each PTable In DSheet.PivotTables
    If PTable.Name = "RegionCountTable" Then
        DSheet.Range(PTable.TableRange2.Address).Delete Shift:=xlUp
        Exit For
    End If
Next PTable

'define the pivot cache
Set PCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange)

'insert a blank pivot table into cell G2 and call it "RegionCountTable"
Set PTable = PCache.CreatePivotTable(TableDestination:=DSheet.Cells(2, 7), TableName:="RegionCountTable")

'insert row fields
With PTable

    With ActiveSheet.PivotTables("RegionCountTable").PivotFields("Region")
     .Orientation = xlRowField
     .Position = 1
    End With

    'create a count for the region
    .AddDataField .PivotFields("Region"), "Count of Region", xlCount
    ActiveWorkbook.ShowPivotTableFieldList = False
End With


End Sub

#1