pivot table update status

[BALLYMOUNT]

I've got a two sheet workbook.
Sheet 1 has a PivotTable linked to a database.
Sheet 2 is blank.

I programatically move through the pivottable drop-down filter which cause the pivottable to recalculate. I then copy and paste those values to sheet 1. I then move onto the next value in the pivot filter and this recalulates the pivot and then I copy/paste (add) to sheet 1 to keep track of cummulative figures. (all this is done in vb/macro)

The problem is that the loop that sets the filter value in the pivottable moves to the next value before the pivottable has finished recalculating. therfore the values I copy/paste may be wrong due to this delay.

Is there a way to ensure pivot-table has finished calculating data before cotinuing on in my loop.

Thanks.

 

[SkipVought]

BALLYMOUNT,

Using the PivotTable filter does NOT cause a refresh. Does your macro force a refresh? It is not necessary UNLESS the source data changes.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[BALLYMOUNT]

I know that its not refreshing the datsource but the actual pivot takes a couple of seconds to rebuild once a value is chosen in the filter. Before it has done this refresh the macro has already moved onto the next value and pluged that into the filter.

 

[SkipVought]

Please post your code.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[BALLYMOUNT]

'Calculate Units For Each StkRef & Paste/Add To Existing Sheet
x = 25 ' First Row Number Of Pivot StockrefList

Do 'loop thru' this until end of list
CurrentCellNumber = "A" & Format(x)
CurrentCellValue = Sheets("SQL_Pivot&quot.Range(CurrentCellNumber).Value
Application.StatusBar = "Working On StkRef:- " & CurrentCellValue & " Cell:- " & CurrentCellNumber

If CurrentCellValue = "Grand Total" Then Exit Do

'set pivot table filters
Sheets("SQL_Pivot&quot.PivotTables("PivotTable2&quot.PivotFields("StockRef&quot.CurrentPage = CurrentCellValue
Sheets("SQL_Pivot&quot.PivotTables("YearEndStk&quot.PivotFields("Code&quot.CurrentPage = CurrentCellValue


Sheets("MonthlyStkFlow&quot.Range("B6:AB18&quot.Select 'Copy/Paste Units
Selection.Copy
Sheets("MonthlyStkFlow&quot.Range("B25&quot.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks:=False, Transpose:=False

Range("AF6:AS18&quot.Select 'Copy/Paste Retail
Selection.Copy
Range("AF60&quot.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks:=False, Transpose:=False

Range("AF25:AS37&quot.Select 'Copy/Paste Cost
Selection.Copy
Range("AF79&quot.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks:=False, Transpose:=False

Range("AF42:AS54&quot.Select 'Copy/Paste VAT
Selection.Copy
Range("AF96&quot.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks:=False, Transpose:=False

Range("AF59:AS71&quot.Select 'copy/paste € Margin
Selection.Copy
Range("AG114&quot.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks:=False, Transpose:=False

x = x + 1 'Increase Cell Row Counter

Loop

Application.StatusBar = "FINISHED : Stock Ageing Calculation Complete !"
Application.StatusBar = False

End Sub

 

[SkipVought]

Do you have cell references that have to recalculate on MonthlyStkFlow as a result of the changes in the Pivot Table because you are not copying anything from the pivot table sheet???

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PHV]

Sheet 1 has a PivotTable linked to a database.
No BackgroundQuery property to set to False ?

Hope This Help
PH.

 

[SkipVought]

PH or BALLYMOUNT

???

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[BALLYMOUNT]

SkipVought,
I forgot to explain that MonthlyStFlow has linked cells which pull values from certain cells in the pivottable.
These linked cells are what I'm copying and pasting into another section of the sheet with the operation : xlAdd to build up a cummulative figure.
The delay in the pivot table is not from pulling data from the database as it does this only once. It's the actual recalculation of the data in the pivot table which takes a couple of seconds each time I change the filter value (programmatically).
I'm assuming that the copy paste operation has happened before the pivot table/linked cells have changed to newer values.

 

[SkipVought]

I kinda figgered that it was a sheet calculation issue from your code.

One way would be to copy directly from the pivottable. I need to know more about how your data and references are arranged.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PHV]

Yet another brute force method:

Application.Wait(Now + TimeValue("0:00:10"))

just after the filter change.

Hope This Help
PH.

 

[BALLYMOUNT]

Thanks for the help guys. I eventually found the glitch. The pivot table was linked to the database via the "data/import extrenal data/import data" menu in Excel XP. This gives the option to use a direct SQL link instead of ODBC, which is what I did. When I manually changed the filter value the pivot-table updated virtually instantly ( within 1 to 2 seconds) , but after writing a check routine in the macro, I discovered that the pivot wasn't updating as frequently when changing the filter programmatically. In fact it was only refreshing about every 30 to 30 seconds..
I've dumped that method and rewrote the macro to use the VLOOKUP function on straight-forward worksheet listing which is linked to the same database.

Regards.