Create a Pivot Table with VBA

Hello,

I'm trying to write a code that will create a pivot table. I'm try to do this using a pivot table cache, but I can't seem to be able to get it working the way I want to.

The attached workbook has two spreadsheets. The first sheet is a set of data, the second is the hoped-for pivot table. I'd like to have a code that generates this pivot table (the generated pivot table should be in a separate worksheet from the data). The row labels = city, values = count of Number of Complaints, values = sum of Number of Complaints.

Thank you for any ideas on how to make this work. I greatly appreciate it!

Hi,

Your cheat sheet is very clever. Thank you for sharing! I tried tweaking a few things and I'm still getting error messages. Here is what I have so far:

Sub CreatePivotTable()

Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String

'pivot table range
SrcData = ActiveSheet.Name & "!" & Range("A:G").Address(ReferenceStyle:=xlR1C1)

'new worksheet
Set sht = Sheets.Add

'pivot table placement
StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlR1C1)

'pivot cache from data
Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SrcData)

'create table
Set pvt = pvtCache.CreatePivotTable( _
TableDestination:=StartPvt, _
TableName:="Table")

'create row label
pvt.PivotFields("city").Orientation = xlRowField

'total sum of number of complaints
Dim pf As String
Dim pf_Name As String

pf = "Complaints"
pf_Name = "Sum of Number of Complaints"

Set pvt = ActiveSheet.PivotTables("PivotTable1")

pvt.AddDataField pvt.PivotFields("Complaints Sum"), pf_Name, xlSum

'total count of number of complaints
pvt.AddDataField pvt.PivotFields("Complaints Counts"), pf_Name, xlCount

End Sub

Please Login or Register  to view this content.

Thanks for the advice, still learning to use the forum tools. I agree, that definitely helps make it readable.

Thanks for the help. I was trying it again today but it the code would stop when it hit the last two lines and it wouldn't finish the pivot table.

Any ideas?

Please Login or Register  to view this content.

I found the error in the last line! Ok, thank you so much for your help on this. These reports are going to run a lot smoother now