Hi All,

I have a excel workbook with a number of tabs, each with an MSQuery from
Access. Each one takes seconds to refresh. I need to refresh most of these
once and then cycle through a list of names, refreshing two more for each
name (these have a parameter looking at an cell to obtain the name), the
workbook is then saved and the process loops round again.

When running the loop once, it works no problem, the data refreshes and has
finished refreshing but once i use the loop properly, the code stops at
'Worksheets("TTP").QueryTables("TTPQuery").Refresh ' and tells me it cannot do
this because it is refreshing in the background.

Please can someone tell me either what is wrong with my code OR how to pause
the code until the refresh finishes (but i don't think it is actually the
problem).

Thanks
Emma

Sub CycleThroughPlanners()
Dim iCount As Long
Dim strPlanner As String
Dim strPath As String

Worksheets("Data").QueryTables("DataPlannerQuery1" ).Refresh
Worksheets("Data").QueryTables("DataPlannerQuery2" ).Refresh
Worksheets("Figures").QueryTables("FiguresPlannerQ uery").Refresh
Worksheets("AMFPData").QueryTables("AMFPQuery1").R efresh
Worksheets("AMFPData").QueryTables("AMFPQuery2").R efresh
Worksheets("AMFPData").QueryTables("AMFPQuery3").R efresh
iCount = 2

Do While Worksheets("Data").Cells(iCount, 12) < ""
strPlanner = Worksheets("Data").Cells(iCount, 12)
Worksheets("Data").Range("C14").Value = strPlanner

'Workbooks(1).RefreshAll

Worksheets("TTP").QueryTables("TTPQuery").Refresh
Worksheets("Pending").QueryTables("PendingQuery"). Refresh

strPath = Worksheets("Data").Range("B16")
Sheets("One To One").Select
Range("A2:AN2").Select
Sheets("One To One").Select
Sheets("One To One").Copy
ActiveWorkbook.SaveAs Filename:=strPath, FileFormat:=xlNormal, Password:="",
WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
iCount = iCount + 1
Loop

End Sub