Pivot Table with vba

Question

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

You have plenty of undefined variables, or probably defined elsewhere but not visible in this scope. In either case it's too hard to debug, I urge you to use Option Explicit and start from there. The error message seems to say that wsA and/or wsB are not defined. — A.S.H

M-- M-- · Accepted Answer · 2017-07-27T21:16:00

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

Pivot Table with vba

1 Answers