CODEWITHSUNDEEP
vbaexcelpivot-table
KateLO
KateLO

Reputation: 67

Pivot Table with vba

I'm trying to create a Pivot table using vba. I need the code to take a sheet and create a pivot using all the data in it. For columns in the pivot i need: "Years" first and then "Document Date" For Row "Arrears after net due date" For values I need the sum of "Amount"

I tried this code below but it gives me an "Object required error" I've never make pivots before so I'm not sure if I'm even on the right track. 

Sub newPVT()

Set Rng = wsA.Range("A1:AA100000")
Set rngData = Range(Rng, Rng.End(xlToRight))
Set rngData = Range(Rng, Rng.End(xlDown))
Set rngB = wsB.Range("C8")

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, 
    SourceData:=rngData, Version:=xlPivotTableVersion14).CreatePivotTable 
    TableDestination:=rngB, TableName:="pvtReportA_B", 
    DefaultVersion:=xlPivotTableVersion14

End Sub

Upvotes: 2

Views: 384

Answers (1)

M--
M--

Reputation: 29203

You need to assign the pivot table to an object:

Sub newPVT()

Dim Rng As Range, rngData As Range, rngB As Range
Dim pvt As PivotTable 


Set wsA = Worksheets("Name of your sheet")
Set Rng = wsA.Range("A1:AA100000")
Set rngData = Range(Rng, Rng.End(xlToRight))
Set rngData = Range(Rng, Rng.End(xlDown))
Set rngB = wsB.Range("C8")

Set pvt = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, 
    SourceData:=rngData, Version:=xlPivotTableVersion14).CreatePivotTable 
    TableDestination:=rngB, TableName:="pvtReportA_B", 
    DefaultVersion:=xlPivotTableVersion14

End Sub

Upvotes: 1

Related Questions