Pivottable refresh problem

[Michael Anderson]

Hi there

I have code that is not refreshing a series of pivottables correctly. After
my code runs I have to right click on each pivottable and select "refresh"
(this works). I have pivottables that are reading data from a querytable.

When I debug and step through the VBA, all of the tables are refreshed
correctly. I've tried using (from
http://www.vbaexpress.com/kb/getarticle.php?kb_id=626) an API declaration to
suspend operation for a specified time (Milliseconds):
"Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)"
within my for loop . As well I've tried the Application.Wait method within my
for loop. I've also tried to explicitly refresh each pivottable by code
individually. I've tried the Activeworkbook.RefreshAll method.



Public Sub RefreshData()
On Error Resume Next

'**Purpose: To Refresh QueryTables and Pivot Tables in this worksheet. It
refers to data
'**in Access tables. It requires that queries are refreshed.

'**Sub called by "button_Click"
'Stop
'Declare Array to hold specified worksheets with Pivot Tables
Dim arrWkshtNames(8) As String
'Declare PivotTable variable
Dim pt As PivotTable
'Declare QueryTable variable
Dim qt As QueryTable
'Declare Worksheet variable
Dim Worksheet As Worksheet
'Declare MessageBox variable
Dim strMsg As String

On Error GoTo ErrorHandler

strMsg = "Pivot Tables Refreshed"

Application.ScreenUpdating = False

'Intialise Array to hold specified worksheets with Pivot Tables
arrWkshtNames(0) = "Sheet1"
arrWkshtNames(1) = "Sheet2"
arrWkshtNames(2) = "Sheet3"
arrWkshtNames(3) = "Sheet4"
arrWkshtNames(4) = "Sheet5"
arrWkshtNames(5) = "Sheet6"
arrWkshtNames(6) = "Sheet7"
arrWkshtNames(7) = "Sheet8"
arrWkshtNames(8) = "Sheet9"

' Update QueryTable Data from Queries
Sheets("Data").Select
'Loop through each QueryTable in each specified worksheet
For Each qt In ActiveSheet.QueryTables
qt.Refresh BackgroundQuery:=True
Next

Sheets("Sheet10").Select
'Loop through each QueryTable in each specified worksheet
For Each qt In ActiveSheet.QueryTables
qt.Refresh BackgroundQuery:=True
Next

Sheets("Sheet11").Select
'Loop through each QueryTable in each specified worksheet
For Each qt In ActiveSheet.QueryTables
qt.Refresh BackgroundQuery:=True
Next

'Loop through specified worksheets with Pivot Tables
For Each Worksheet In Sheets(arrWkshtNames)
Worksheet.Activate
'Loop through each PivotTable in each specified worksheet
For Each pt In ActiveSheet.PivotTables
pt.RefreshTable
Next
Next

'leave specified page open
ActiveWorkbook.Worksheets("Sheet10").Select

Application.ScreenUpdating = True

'MessageBox when task completed
MsgBox (strMsg)

'Finish code
Exit Sub

 

[Mike Fogleman]

Your data needs to be in a sheet level named range and your PT needs to
refer to that range name as it's data source. Then ThisWorkbook.RefreshAll
will work.
Named Range = "Database" Refers to =SHEET1!$A$1:$F$422 for example

Right-click your Pivot Table and bring up the Wizard. Click back one screen
to define your data source as Sheet1!Database.
Repeat for all data and pivot tables on all sheets.

Mike F

 

[Michael Anderson]

Hi

I'd already done what you'd said.

Answer was ...

qt.Refresh BackgroundQuery:=False


--
Thank You in Advance,

Michael Anderson

Your data needs to be in a sheet level named range and your PT needs to
refer to that range name as it's data source. Then ThisWorkbook.RefreshAll
will work.
Named Range = "Database" Refers to =SHEET1!$A$1:$F$422 for example

Right-click your Pivot Table and bring up the Wizard. Click back one screen
to define your data source as Sheet1!Database.
Repeat for all data and pivot tables on all sheets.

Mike F