Set myPivotTable = ActiveWorkbook.PivotCaches.Create(
SourceType:=xlDatabase,
SourceData:=mySourceWorksheet.Name & "!" & mySourceData).CreatePivotTable(
TableDestination:=myDestinationWorksheet.Name & "!" & myDestinationRange,
TableName:="Sheet1NewSheet")
The above code gives me
runtime error 5 that invalid procedure call or argument.
Faced Similar situation, found this code somewhere on web long back. Credits to the unknown person.
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("Ageing")
'.Orientation = xlColumnField
'.Position = 1
'End With
'Insert Row Fields
With ActiveSheet.PivotTables("PRIMEPivotTable").PivotFields("Eid")
.Orientation = xlRowField
.Position = 1
End With
'Insert Data Field
With ActiveSheet.PivotTables("PRIMEPivotTable").PivotFields("Eid")
.Orientation = xlDataField
.Position = 1
.Function = xlCount
.Name = "Name of ur choice"
End With
'Format Pivot Table
ActiveSheet.PivotTables("PRIMEPivotTable").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("PRIMEPivotTable").TableStyle2 = "PivotStyleMedium9"
End Sub
Hopefully, all of your objects are defined and set correctly (as in my code below).
Option Explicit
Sub CreatAutoPivot()
Dim myPivotTable As PivotTable
Dim myPivotCache As PivotCache
Dim myDestinationWorksheet As Worksheet
Dim mySourceData As Range
Dim myDestinationRange As Range
' === set of Parameters I've given my objects: for internal tests ===
Set myDestinationWorksheet = ThisWorkbook.Sheets("Pivot_Sht") ' modify according to your sheet's name
Set myDestinationRange = myDestinationWorksheet.Range("A1") ' modify to where you want to place your Pivot-Table
Set mySourceData = ThisWorkbook.Sheets("Pivot_Data").Range("A1:E10") ' modify according to your Source Data Range and worksheet's name
' set the Pivot Cache
Set myPivotCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mySourceData.Address(False, False, xlA1, xlExternal))
' set the Pivot Table
Set myPivotTable = myDestinationWorksheet.PivotTables.Add(PivotCache:=myPivotCache, TableDestination:=myDestinationRange, TableName:="Sheet1NewSheet")
End Sub
