VBA Created Pivot Table needs manual refresh

[KG Old Wolf]

Following is a sub procedure that proudces the desired pivot table just fine if I step through its execution using "F8", if I use "F5" it shows none of the detail.

What's really odd is that the detail is there... I look at the filter carot and the fields are populated but they just don't appear - it looks like a blank pivot table. I hit "refresh" and all data then appears. Again, it works just fine in step mode.

I even inserted "ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh" in the macro and it still requires a manual "refresh"

Anyone see what I am clearly missing?

Thanks for the advice,
KG Old Wolf
--------------------------------------------------------
'
Sub CreatePivot()
'
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("RVU Output")
'
'
' Delete Any Prior Pivot Tables
'
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
'
'
' 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.Address)
'
'
' Create the Pivot Table from the Pivot Cache
'
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
Cells(1, FinalCol + 2), TableName:="PivotTable1")
'
'
' Turn Off Updating While Building the Table
'
PT.ManualUpdate = True
'
'
' Set Up the Row and Column Fields
'
PT.AddFields RowFields:="MD Name", ColumnFields:="Location Abbr."
'
'
' Set Up the Data Fields
'
With PT.PivotFields("YTD RVU Extended")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With
'
'
' Calculate the Pivot Table
'
PT.ManualUpdate = False
PT.ManualUpdate = True
'
'
' Format the Pivot Table
'
PT.ShowTableStyleRowStripes = True
PT.TableStyle2 = "PivotStyleMedium10"
'
'
' Display Results
'
WSD.Activate
Range("J2").Select
'
'
'
End Sub
---------------------------------------------------

 

[dragonad]

I'm having the same problem. Does anyone have the solution to this?

Thanks

 

[Tardis]

Same problem here as well. The problem seems to come up for a number of people fairly regularly. Has anyone found a solution?

 

[Tardis]

I figured it out. The ".Refresh" and ".RefreshTable" methods only refresh the cache with source data. You need to use the ".Update" method to update the pivot table on the worksheet.

So something like:

ActiveSheet.PivotTables(1).Update

Cheers!