Excel Macro Refresh Problem

[chouna]

I have an Excel sheet with a user form that requests an entry from the user. The input is then assigned to a cell in the worksheet which serves as a parameter for my external query, which in turn, is the source of my pivot table. Everything is fine except that when I refresh my pivot table (through code), it does not do it. Here is my code:


Private Sub CommandButton1_Click()

Range("D2").Select
ActiveCell.Value = txtAccount.Value
Range("A9").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
Unload Me

End Sub


I assign the user-input to cell D2 which serves as the parameter for my query. If I don't run the first two lines (which means I simply am refreshing the pivot), the pt refreshes. However, when I run the entire routine, pt does not refresh. How do I code it to refresh the pt?

Happy Valentine to all.

 

[xlbo]

You would need to refresh the external data source as well surely.

To be honest though, why not just query directly into the pivottable ??

As a last point, you need
PivotTables("PivotTable2").REFRESHTABLE

that should work better than just refreshing the cache

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[chouna]

Hi Geoff,

I know somebody was bound to ask the question why I don't manipulate the external data as a pivot table. You see, in my Excel sheet, I have adjacent columns that require complex calculation i.e.INDEX(MATCH()) combination to determine number of months brackets in Aging of Receivable analysis. This is easily done using the option of "Fill down formulas in columns adjacent data", an option which is not there for pivot.

My data updates automatically when user enters a value using the options "Get the value from following cell" and "Refresh automatically when value changes" in Parameter options. They work just fine and update my worksheet as expected.

The problem seems to be synchronization. When the form pops up for user to enter a new value, the pivot updates using the previous value.

I added the RefreshTable method as you suggested but still did not get the pt to refresh.

Please tell me there is a way out of this or I have to create another command button just to refresh.

Thanks

 

[01Patrik]

Why not put the refresh-part in a worksheet_change event instead?

Isect = application.intersect(targetrange("D2"))
if Isect is nothing then exit sub

sheets("XXX").PivotTables("PivotTable2").PivotCache.Refresh

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[xlbo]

I suspect that the external data refresh is happening at the same time as the pivottable refresh

Try setting the external data BackgroundQuery property to false - this should stop anything else happening until the external data query is properly finished

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[xlbo]

and I totally understand the external data>pivottable btw - I use that functionality all the time - just wanted to check that there was a good reason for not putting it straight into a pivot !

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question