On 07/02/2012 17:47, Revenue wrote:
Ok, this is the full routine that works about once every four or five
times. And, no I don't have any idea how it works part of the time,
but it has to work every time without fail in order to be useful.

Sub CA_Upload()
Dim TransmjeReqFile As String
TransReqFile = Range("Transreqfile").Value
Application.DisplayAlerts = False
Call Unhide
Call HideZeros
Range("Start").Offset(3, 0).CurrentRegion.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlValues
Range("G:G, P:R, AF:AG").Delete
Application.CutCopyMode = False
Application.DisplayAlerts = True
Range("A1").CurrentRegion.Select
SendKeys ("/xD1" + "{Tab 6}" + TransReqFile + "{Tab 2}" +
"{Enter}"), True
On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.Close
ActiveWorkbook.Close
Application.DisplayAlerts = True

End Sub

SendKeys is dicing with death, but if you have to do it that way I would
be inclined to guess there is a race condition where some of the
preceding lines are still executing when the keys are sent. Possibly
meaning that the region you want is not actually selected in time.

Most of the intermittent faults I have seen have been charting and
graphics related rather than with dialogue - but intermittent faults
usually mean timing or focus problems.

Add a WasteTime and/or DoEvents macro between the line before and after
the Sendkeys call and cross your fingers. XL2007 and 2010 seem more
prone to race conditions in VBA code than previous versions :(


--
Regards,
Martin Brown