Bill

Just trying to run your code, and a message comes back saying its looking
for the Data Source. One thing that is different with your code is the
following, which it doesn't have, does the code need it?

DefaultDir=C:\timepoint;Driver={Microsoft Access Driver
(*.mdb)};DriverId=281;FIL=MS Access;M" _), Array( _

"axBufferSize=2048;MaxScanRows=8;PageTimeout=5;Saf eTransactions=0;Threads=3;
UID=admin;UserCommitSync=Yes;" _
)),


"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