I'm having trouble with the "Refresh" part of the macro below. When the macro triggers, if I watch it, the screen just keeps flickering. When I hit escape, I get a Run-Time 1004 error saying "RefreshTable method of PivotTable Class failed". It's a simple bit of code, so I don't know why it would be messing up. Any help troubleshooting this is greatly appreciated.
Sub PivotUpdate()
Dim LastRow As Long
Dim PT As PivotTable
With Sheets("ProjPivot")
Set PT = .PivotTables("PivotTable1")
PT.RefreshTable
LastRow = .Range("C" & Rows.Count).End(xlUp).Row 'this tells where our last row is
.Range("H4:M" & LastRow).FormulaR1C1 = "=RC[-7]"
.Range("H4:M" & LastRow).Value = .Range("H4:M" & LastRow).Value
.Columns("H:I").Replace What:="0", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Range("H5:I" & LastRow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Range("H5:I" & LastRow).Value = Range("H5:I" & LastRow).Value
.Columns("K:M").Replace What:="Sum of ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Range("H4:M" & LastRow).Copy
Sheets("MFR Adjustments").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
End Sub
Bookmarks