Results 1 to 10 of 10

Refresh Pivot - caught in a loop

Threaded View

jomili Refresh Pivot - caught in a... 11-16-2011, 01:54 PM
jomili Re: Refresh Pivot - caught in... 11-16-2011, 02:54 PM
Steffen Thomsen Re: Refresh Pivot - caught in... 11-16-2011, 02:57 PM
jomili Re: Refresh Pivot - caught in... 11-16-2011, 03:11 PM
jomili Re: Refresh Pivot - caught in... 11-16-2011, 03:23 PM
JieJenn Re: Refresh Pivot - caught in... 11-16-2011, 03:28 PM
jomili Re: Refresh Pivot - caught in... 11-16-2011, 03:51 PM
Steffen Thomsen Re: Refresh Pivot - caught in... 11-16-2011, 03:33 PM
Steffen Thomsen Re: Refresh Pivot - caught in... 11-16-2011, 03:53 PM
jomili Re: Refresh Pivot - caught in... 11-18-2011, 09:42 AM
  1. #1
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    4,065

    Refresh Pivot - caught in a loop

    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
    Last edited by jomili; 11-18-2011 at 09:43 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1