CODEWITHSUNDEEP
vbaexcelpivot-table
yashika vaish
yashika vaish

Reputation: 201

Issue in Creating Pivot table through VBA

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.

Upvotes: 0

Views: 364

Answers (2)

Arjeel
Arjeel

Reputation: 148

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

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33682

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

Upvotes: 1

Related Questions