Excel Pivot tables refresh
 

I have a few database queries in a spreadsheet, each query is then
referenced by it's own pivot table. I then have a VBA button on the main
"Report" page that when pressed refreshes all "ThisworkBook.RefreshAll".
Problem is that I have to press is more than once. I'm guessing it has
something to do with the order in which I added the queries and pivot
tables, but is there a way to have the VBA code refresh all correctly.
I have tried putting the code in twice but it runs the second line before
the first one has completed all the queries/tables.

Dean

--

http://www.dkso.co.uk/
http://homepage.ntlworld.com/dkso

Hi, Dkso. Why don't you try something like this, which refreshes when the
sheet is activated.
http://www.vbaexpress.com/kb/getarticle.php?kb_id=80
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"Dkso" wrote in message
...
I have a few database queries in a spreadsheet, each query is then
referenced by it's own pivot table. I then have a VBA button on the main
"Report" page that when pressed refreshes all "ThisworkBook.RefreshAll".
Problem is that I have to press is more than once. I'm guessing it has
something to do with the order in which I added the queries and pivot
tables, but is there a way to have the VBA code refresh all correctly.
I have tried putting the code in twice but it runs the second line before
the first one has completed all the queries/tables.

Dean

--

http://www.dkso.co.uk/
http://homepage.ntlworld.com/dkso

You could refresh the queries, then refresh the pivot caches, e.g.:

'===============================
Sub MyRefreshAll()

Dim ws As Worksheet
Dim qry As QueryTable
Dim pc As PivotCache

For Each ws In ActiveWorkbook.Worksheets
For Each qry In ws.QueryTables
qry.BackgroundQuery = False
qry.Refresh
Next qry
Next ws

For Each pc In ActiveWorkbook.PivotCaches
pc.Refresh
Next pc

MsgBox "Queries and Pivot caches have been refreshed"

End Sub
'===============================

Dkso wrote:
I have a few database queries in a spreadsheet, each query is then
referenced by it's own pivot table. I then have a VBA button on the main
"Report" page that when pressed refreshes all "ThisworkBook.RefreshAll".
Problem is that I have to press is more than once. I'm guessing it has
something to do with the order in which I added the queries and pivot
tables, but is there a way to have the VBA code refresh all correctly.
I have tried putting the code in twice but it runs the second line before
the first one has completed all the queries/tables.

Dean



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

I knew someone out there would have a good suggestion.

Thanks, I'll give them a go.

Dean
"Dkso" wrote in message
...
I have a few database queries in a spreadsheet, each query is then
referenced by it's own pivot table. I then have a VBA button on the main
"Report" page that when pressed refreshes all "ThisworkBook.RefreshAll".
Problem is that I have to press is more than once. I'm guessing it has
something to do with the order in which I added the queries and pivot
tables, but is there a way to have the VBA code refresh all correctly.
I have tried putting the code in twice but it runs the second line before
the first one has completed all the queries/tables.

Dean

--

http://www.dkso.co.uk/
http://homepage.ntlworld.com/dkso