I have a macro that runs a query against an external data source and then
updates the range of a pivot table based on the number of rows and columns
the query retutrns. When I run the macro in debug mode everything works
fine. When I run it from the command button the range is not updated. I
suspect that since the query is running in background mode, that the range
calculations happen before the results are returned. What I need to be able
to do is have the macro wait until the query results are returned before
continuing on. Is there a way to do this? Here is the macro:

Option Explicit

Sub Kintana_Update()
'
' Kintana_Update Macro
' Macro recorded 02/05/2010
' This macro runs the data query to Relay and updates
' the range on the Service Level Work pivot table with the
' number of rows returned from the query

Dim wks As Worksheet
Dim LastRow As Long
Dim LastCol As Long
'
' Query Relay
Sheets("Relay Data").Select
Range("A2").Select
Selection.QueryTable.Refresh BackgroundQuery:=True

' Calculate the range of data returned from the query
With ActiveSheet
LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

' Refresh the main pivot table with the new range of data
Sheets("PF Kintanas Pivot Tables").Select
Range("D1").Select
ActiveSheet.PivotTables("Service Level Work").PivotSelect "",
xlDataAndLabel, _
True
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'Relay Data'!R2C1:R" & LastRow & "C" & LastCol
Sheets("PF Kintanas Pivot Tables").PivotTables("Service Level
Work").PivotCache.Refresh
End Sub

Try changing the BackgroundQuery to false
--
HTH...

Jim Thomlinson


"tjfwestcoast" wrote:

I have a macro that runs a query against an external data source and then
updates the range of a pivot table based on the number of rows and columns
the query retutrns. When I run the macro in debug mode everything works
fine. When I run it from the command button the range is not updated. I
suspect that since the query is running in background mode, that the range
calculations happen before the results are returned. What I need to be able
to do is have the macro wait until the query results are returned before
continuing on. Is there a way to do this? Here is the macro:

Option Explicit

Sub Kintana_Update()
'
' Kintana_Update Macro
' Macro recorded 02/05/2010
' This macro runs the data query to Relay and updates
' the range on the Service Level Work pivot table with the
' number of rows returned from the query

Dim wks As Worksheet
Dim LastRow As Long
Dim LastCol As Long
'
' Query Relay
Sheets("Relay Data").Select
Range("A2").Select
Selection.QueryTable.Refresh BackgroundQuery:=True

' Calculate the range of data returned from the query
With ActiveSheet
LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

' Refresh the main pivot table with the new range of data
Sheets("PF Kintanas Pivot Tables").Select
Range("D1").Select
ActiveSheet.PivotTables("Service Level Work").PivotSelect "",
xlDataAndLabel, _
True
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'Relay Data'!R2C1:R" & LastRow & "C" & LastCol
Sheets("PF Kintanas Pivot Tables").PivotTables("Service Level
Work").PivotCache.Refresh
End Sub