Unable to create pivot ina new worksheet in VBA

Question

I recorded a macro in which I want to create a pivot table into a new worksheet. I am using 2010 version.

I have the "Run time error 5" Invalid procedure call or argument" error when I want to run a macro. Please see the code. It creates the new sheet so is it not fine?

Range("A1").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R17445C24", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion12

Sheets("Sheet4").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Bnlunit")
    .Orientation = xlPageField
    .Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Period")
    .Orientation = xlColumnField
    .Position = 1
End With
ActiveWindow.SmallScroll Down:=12
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Amount"), "Sum of Amount", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Hdaccount_agr_3(T)")
    .Orientation = xlRowField
    .Position = 1
End With
ActiveWindow.SmallScroll Down:=-33

End Sub

Answer 1

The problem is because of sheet name and not deleting those sheets. I think the below code could be of your help

Sub Macro1()
'
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long

'Delete Preivous Pivot Table Worksheet & Insert a New Blank Worksheet With Same Name
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("Raw Data")

'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="PRIMEPivotTable")

'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable_(TableDestination:=PSheet.Cells(1, 1), TableName:="PRIMEPivotTable")

'Insert Column Fields
'With ActiveSheet.PivotTables("PRIMEPivotTable").PivotFields("ColumnName")
 '.Orientation = xlColumnField
 '.Position = 1
'End With

'Insert Row Fields
With ActiveSheet.PivotTables("PRIMEPivotTable").PivotFields("RowName")
 .Orientation = xlRowField
 .Position = 1
End With

'Insert Data Field
With ActiveSheet.PivotTables("PRIMEPivotTable").PivotFields("Field 1")
 .Orientation = xlDataField
 .Position = 1
 .Function = xlCount
 .Name = "Name of your choice"
End With


End Sub

Answer 2

Try the code below, explanations inside the code's comments:

Option Explicit

Sub VBAPivot()

Dim Sht1 As Worksheet
Dim NewSht As Worksheet
Dim PvtCache As PivotCache
Dim PvtTbl As PivotTable

Dim PvtRange As Range
Dim LastRow As Long

Set NewSht = ThisWorkbook.Sheets.Add ' add new sheet

Set Sht1 = ThisWorkbook.Worksheets("Sheet1")
With Sht1
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    ' set the PivotCach DataSource Range
    Set PvtRange = .Range("A1:X" & LastRow)
End With

' Set the Pivot Cache
Set PvtCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, PvtRange.Address(False, False, xlA1, xlExternal))

' create a new Pivot Table in the new added sheet, in "A3"
Set PvtTbl = NewSht.PivotTables.Add(PivotCache:=PvtCache, TableDestination:=NewSht.Range("A3"), TableName:="PivotTable1")

With PvtTbl ' modify Pivot-Table properties

    With .PivotFields("Bnlunit")
        .Orientation = xlPageField
        .Position = 1
    End With
    With .PivotFields("Period")
        .Orientation = xlColumnField
        .Position = 1
    End With

    ' add Field as Sum of
    .AddDataField .PivotFields("Amount"), "Sum of Amount", xlSum

    With .PivotFields("Hdaccount_agr_3(T)")
        .Orientation = xlRowField
        .Position = 1
    End With
End With

End Sub