Excel Pivot Table with a macro

Question

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.

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

Answer 1

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

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

Answer 2

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

Afterwards, it will create a new PivotTable on "GF Response Detail R" sheet, with the updated data in Column "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

Answer 3

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