Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Blank Cells in Pivot Tables | Excel Discussion (Misc queries) | |||
Pivot Tables | Excel Discussion (Misc queries) | |||
How can deleted data reappear in a refreshed pivot table in Excel | Excel Discussion (Misc queries) | |||
Product Function in Pivot Tables from Multiple Consolidation Range | Excel Worksheet Functions | |||
convert excel list to pivot table | Excel Discussion (Misc queries) |