Thanks Bill

I have given up, broken it down in to an 'add query' and then a Refresh one.
I had been running the 'add' over and over again, but all I required
apparently was refresh. Thanks for the tightened code


"Bill Manville" wrote in message
...
John wrote:
Range("S2.S2").Copy


That statement should cause an error.
Range("S2").Copy
would be fine.

You have asked it to carry out the query in the background which means
that the query may not have completed by the time you try to copy and
paste the formula.

The code could be tightened up quite a bit as there is quite a lot of
unnecessary selecting being done, but that's the way the macro recorder
works.

For the record, I would reduce it to something like this:

Sub Refresh_Timepoint()
Sheets("Database").Select
ActiveSheet.UsedRange.ClearContents
With ActiveSheet.QueryTables.Add( _
Connection:="ODBC;DBQ=C:\timepoint\Timepoint_be.MD B", _
Destination:=Range("A1"))
.CommandText = "SELECT StaffNum, DeptNum, PayrollNum, " & _
"ContractType, EmployeeType, Forename, Surname, EmpAddress1, " & _
"EmpAddress2, EmpAddress3, EmpAddress4, DateOfBirth, " & _
"TerminationDate, TerminationPeriod, CommencementDate, " & _
"CommencementPeriod, PayRate, NatInsNum " & _
"FROM Employees ORDER BY Surname"
.Name = "Query from Timepoint"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

Application.ScreenUpdating = False

Application.Calculation = xlManual

Columns("L:M").NumberFormat = "DD/MM/YY"

Columns("N:N").NumberFormat = "####-##"

Columns("o:o").NumberFormat = "DD/MM/YY"

Columns("P:P").NumberFormat = "####-##"

Columns("Q:Q").NumberFormat = "?#,##0.00"

With Columns("B:B")
.Replace What:="1", Replacement:="Crew", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="99", Replacement:="Mgr", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
End With

With Columns("D:D")
.Replace What:="10", Replacement:="Crew F/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="11", Replacement:="Crew P/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="12", Replacement:="Mgr F/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="13", Replacement:="Mgr P/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
End With

Range("S2", Cells(Range("G2").End(xlDown).Row, "S")).Formula = _
"=PROPER(F2&"" ""&G2)"

Application.Calculation = xlAutomatic

Sheets("Home").Select
Range("A1").Select

Application.ScreenUpdating = True

End Sub


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup