I've written some VBA to create a pivot table. It works fine. The only problem is that when it's first created, it only shows the headings. As soon as I click on one of them, then all of the values show-up.
I've tried refreshing the Pivot Table in the code, but that doesn't work.
Sub createPivotTable()
Dim WSD 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 Pricing")
' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).row
FinalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=PRange)
Sheets.Add
ActiveSheet.Name = "SKU Pivot"
Set PT = PTCache.createPivotTable(TableDestination:=Worksheets("SKU Pivot").Cells(2, 2), _
TableName:="PivotTable1")
PT.ManualUpdate = True
' Set up the row & column fields
PT.AddFields RowFields:=Array("Option Name & Description", "SKU", "QTY"), _
ColumnFields:="Style Name"
' Set up the data fields
With PT.PivotFields("SKU Net Price")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With
For Each PT In Worksheets("SKU Pivot").PivotTables
PT.RefreshTable
Next PT
End Sub
Bookmarks