VBA Pivot table creation

Question

Hi All,

I am very new to VBA and I am having some difficulties with the below code. I receive a run time error 5 - Invalid procedure or argument.

Debug portion is also highlighted below.

Any help would be greatly appreciated

Sub CreatePivotTable()

Dim sht As Worksheet

Dim pvtCache As PivotCache

Dim pvt As PivotTable

Dim StartPvt As String

Dim SrcData As String

Set usdws = Worksheets("usd cash liabilities")

Set UsdR = Worksheets("usd Result")

'data range of pivot table

SrcData = usdws.Name & "!" & Range("A1:R100").Address(ReferenceStyle:=xlR1C1)

'pivot location

StartPvt = UsdR.Name & "!" & Range("A3").Address(ReferenceStyle:=xlR1C1)

'create pivot cache from source data

Set pvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData)

Set pvt = pvtCache.CreatePivotTable(TableDestination:=StartPvt, TableName:="PivotTable1")

Thanks in advance

Answer 1

Hi,

sceanario:

create a new pivot table in sheet2

data in sheet1

try this...

Sub Add_New_PivotTable()

'May 29, 2017

Dim ws1 As Worksheet

Set ws1 = Sheets(**"Sheet1")' << data in sheet1 / '**usd cash liabilities'

Dim ws2 As Worksheet

Set ws2 = Sheets**("Sheet2") ' << pivot table in sheet2 /  '**usd Result'

'

'## delete old pivot table from sheet2/if exists

On Error Resume Next

ws2.PivotTables(1).TableRange2.Clear

'

Dim pc As PivotCache

Dim pt As PivotTable

'## create pivot cache

Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, ws1.Range("A1:R100")) '<< data in sheet1

'## create pivot table

Set pt = ws2.PivotTables.Add(PivotCache:=pc, Tabledestination:=ws2.Range("A3")) '<< pivot table / top left cell

pt.Name = "PivotTable1"

'....

'next code here....

'.......

Answer 2

Thank you VM for your help