Prev: need formula help
Next: KEEP THE CELL MARKER FROM MOVING DOWN, AFTER ENTER
From: tjfwestcoast on 5 Feb 2010 15:16
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
From: Jim Thomlinson on 5 Feb 2010 15:35
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
 | 
Pages: 1
Prev: need formula help
Next: KEEP THE CELL MARKER FROM MOVING DOWN, AFTER ENTER