My workbook contains several worksheets with queries to external SQL Server
database. All queries use background updates. Each query uses a parameter
for the SQL query string. The parameter value is obtained from E2
Reports!B4. Manually changing this cell returns results on worksheet Job
Summary in range("A6:K6"). This works great!

Worksheet Order Details contains a list of job numbers in column B. I wrote
a VBA sub that loops through these jobs, changing E2 Reports!B4 for each job.
I then call the Application.RefreshAll function. Finally, I copy "Job
Summary!A6:K6" to a row on worksheet Order Summary. If the next job is the
same order, I copy with a paste special to add the results.

The problem occurs when running the VBA sub. If I step through the code,
waiting for the RefreshAll function to finish, the correct values are
returned. If I step through fast, without waiting for the RefreshAll, the
values for some jobs are copies of the previous job, but other jobs are
correct. If I hold the F8 key down or hit the F5 key, all values are the
same as the first job.

I've tried DoEvents, turning off screen updates, and turning off background
updates.

Any ideas?

Code:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
If Target.Name = "Order Summary" Then
' rj references position on Job Summary sheet
Dim rj As Integer
' ro references position on Order Summary sheet
Dim ro As Integer
rj = 6
ro = 6
' clear all rows
Sheets("Order Summary").Range("a6:a65536").EntireRow.Clear
Application.CutCopyMode = False
' turn off screen updating to avoid flicker
Application.ScreenUpdating = False
' copy unique orders from order details
While Len(Sheets("Order Details").Range("b" & rj)) 0
' copy customer and order #
Sheets("Order Details").Range("a" & rj & ":b" & rj).Copy
Destination:=Sheets("Order Summary").Range("a" & ro)
' get job summary for first job in this order
GetJobSummary Sheets("Order Details").Range("c" & rj)
Sheets("Order Summary").Range("c" & ro).PasteSpecial
xlPasteValuesAndNumberFormats
' escape from copy mode
Application.CutCopyMode = False
rj = rj + 1
' get summary for next jobs for same order
While Sheets("Order Details").Range("b" & rj) = Sheets("Order
Details").Range("b" & rj - 1)
' add next job summary results to this order
GetJobSummary Sheets("Order Details").Range("c" & rj), True
Sheets("Order Summary").Range("d" & ro).PasteSpecial
xlPasteValuesAndNumberFormats, xlPasteSpecialOperationAdd
' escape from copy mode
Application.CutCopyMode = False
rj = rj + 1
Wend
ro = ro + 1
Wend
' turn screen updating back on
Application.ScreenUpdating = True
End If
End Sub

Private Sub GetJobSummary(JobNo As String, Optional NoOrderTotal As Boolean)
If IsMissing(NoOrderTotal) Then NoOrderTotal = False
' load jobno in query parameter field
Sheets("E2 Reports").Range("B4").FormulaR1C1 = JobNo
' refresh all queries
DoEvents
ActiveWorkbook.RefreshAll
' copy data to return to calling procedure
If NoOrderTotal Then
' copy just details
Sheets("Job Summary").Range("B6:K6").Copy
Else
' copy order total with details
Sheets("Job Summary").Range("A6:K6").Copy
End If
End Sub