Pivot Table Seems to get corrupted when updating the pagefields with VBA

[EDGE99]

I am having some very strange issues when trying to update a pivot tables pagefields using VBA. The pagefields do get updated with the correct information but the output of that is returned in the Pivot Table is not correct. When I create a new PT and use the same selection criteria I get the correct output. I seems like when I do it with VBA it corrupts the PT.

I have also tried clearing the pivot cache and refreshing the pivots prior to setting the pagefields and I still get that same issue.

Sub Main() 
    Dim ws As Worksheet 
    Dim wsPT As Worksheet 
     
    Dim pt As PivotTable 
     
    Set ws = ThisWorkbook.Sheets("Config") 
    Set wsPT = ThisWorkbook.Sheets("DiscountPivots") 
    Set pt = wsPT.PivotTables("PivotTable1") 
     
     '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
     ' Pivot Table PaigField Selectors
     '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    xDealSize = ws.Range("B5").Value 
    xLookup = ws.Range("B8").Value 
     '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
     ' Update Pivot Tables
     '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
     ' Set Totals Pivot
    pt.PivotFields("Lookup").CurrentPage = xLookup 
    pt.PivotFields("Deal_Size2").CurrentPage = xDealSize 
     
    Set ws = Nothing 
    Set wsPT = Nothing 
    Set pt = Nothing 
End Sub

At first it seemed to be working fine but once I went to validate the data the pivot table was not showing the correct data although the pagefields have been updated to the correct information. I verified this by creating another pivot table and manually selected the correct values for the pagefields and it showed the correct information. The VBA updated PT only came back with a few records where as the manually selected one brings back all of the data.

Does anyone have any idea how to get around this? I spent several hours trying to resolve and looking like all that work will be waisted.

Thanks

 

[genomon]

Could it be a refresh issue? There are settings and VBA to look at here:

http://www.ozgrid.com/VBA/pivot-table-refresh.htm

Beir bua agus beannacht!

 

[EDGE99]

genomon I don't believe it is a refresh issue as I have tried this as well and still gives the wrong data. It actually seems like it corrupts the pivot table because even when I go and manually try and modify and refresh it afterwards it still is messed up.

 

[SkipVought]

hi,

Also I would not SET object variables unnecessarily...

Sub Main()
    Dim ws As Worksheet
     
    Set ws = ThisWorkbook.Sheets("Config")
    
    With ThisWorkbook.Sheets("DiscountPivots")
        With .PivotTables("PivotTable1")
     '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
     ' Update Pivot Tables
     '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
     ' Set Totals Pivot
            .PivotFields("Lookup").CurrentPage = ws.Range("B5").Value
            .PivotFields("Deal_Size2").CurrentPage = ws.Range("B8").Value
            .PivotCache.Refresh
        End With
    
    End
    
    Set ws = Nothing
End Sub

 

[combo]

Do you have renamed items in pivot table? If so, see GlennUK's code in

http://www.mrexcel.com/forum/excel-questions/530081-rename-pivot-table-items.html

combo

 

[EDGE99]

Skip do you think setting those objects could be causing other issues? I will play around with this to see if it helps.

combo there are no renamed items in the pivot table.

 

[SkipVought]

Old Pivot Items causing PivotTable problems faq68-7102