CODEWITHSUNDEEP
vbaexcel
GCC
GCC

Reputation: 295

How to create a pivot table in excel VBA?

I am trying to create a pivot table but I am getting an error with my last line of code.

Dim WSD As Worksheet
Dim WSD2 As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Dim FinalCol As Long
Set WSD = Worksheets("SKU Sum")
Set WSD2 = Worksheets("Finelines")

' Select the data for pivot table

FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange)

Set PT = WSD.PivotTables.Add(PivotCache:=PTCache, TableDestination:=WSD2.Range(A1), TableName:="Pivotab")

Any help would be greatly appreciated.

Thanks,

G

Upvotes: 0

Views: 524

Answers (2)

Shai Rado
Shai Rado

Reputation: 33682

Replace your current PivtoCache line:

Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange)

With:

Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange.Address(False, False, xlA1, xlExternal))

Also, you are getting the error in the last line, because you are setting the location of the Pivot-Table WSD2.Range(A1) , the string inside the brackets need to have ". So also modify it to WSD2.Range("A1")

Upvotes: 0

Sam
Sam

Reputation: 5721

Replace the last line with

Set PT = PTCache.CreatePivotTable(TableDestination:=WSD2.Range("A1"), TableName:="Pivotab")

Upvotes: 1

Related Questions