Create Pivot Table in an existing worksheet

[sn152]

Hi All,

I am trying to create a pivot table in an existing worksheet in the same workbook.

I have the below code. But when I run it shows an error stating invalid procedure call or argument. I have marked the line where I get the error in bold.

Please help me with this. Thanks in advance.

Sub Create_PivoteTable()

Dim PvtTbl1 As PivotTable
Dim wsData1 As Worksheet
Dim rngData1 As Range
Dim PvtTblCache1 As PivotCache
Dim wsPvtTbl1 As Worksheet
Dim pvtFld1 As PivotField


Set wsData1 = Worksheets("Raw Data")
Set wsPvtTbl1 = Worksheets("Pivot - raw")

lastRow = wsData1.Cells(Rows.Count, 1).End(xlUp).Row
lastColumn = wsData1.Cells(1, Columns.Count).End(xlToLeft).Column
Set rngData1 = wsData1.Cells(1, 1).Resize(lastRow, lastColumn)


[B]ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData, Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:=wsPvtTbl1.Range("A1"), TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12[/B]
Set PvtTbl1 = wsPvtTbl1.PivotTables("PivotTable1")


'add row, column and page (report filter) fields:

Set pvtFld1 = PvtTbl1.PivotFields("Name")
pvtFld1.Orientation = xlRowField

Set pvtFld1 = PvtTbl1.PivotFields("Team")
pvtFld1.Orientation = xlRowField
pvtFld1.Position = 1

Set pvtFld1 = PvtTbl1.PivotFields("Duration")
pvtFld1.Orientation = xlColumnField

With PvtTbl1.PivotFields("Sales")
.Orientation = xlDataField
.Function = xlCount
.Position = 1
End With

PvtTbl1.ManualUpdate = False

End Sub

 

[jeffreyweir]

Can you upload a sample file with some dummy data so we can step through the code as it runs?

 

[sn152]

Hi Jeffrey,

I got it corrected myself. Thank you