Something like (not validated):

Public Sub ProcessData()
Const sPATH As String = "<your path"
Const sFILE1 As String = "aerial survey data.xls"
Const sFILE2 As String = "report data.xls"
Const sERR As String = "Error opening file" & vbNewLine & _
vbNewLine & "$$" & vbNewLine & vbNewLine & "No data copied"
Dim wbOne As Workbook
Dim wbTwo As Workbook
Dim rCopy As Range
Dim i As Long
On Error Resume Next
OpenFIle sFILE1, sPATH
Set wbOne = Workbooks(sFILE1)
OpenFIle sFILE2, sPATH
Set wbTwo = Workbooks(sFILE2)
On Error GoTo 0
If Not wbOne Is Nothing Then
If Not wbTwo Is Nothing Then
For i = 1 to ThisWorkbook.Worksheets.Count
Set rCopy = ThisWorkbook.Sheets(i).Range("A2:E2")
With wbOne.Sheets(1)
.Cells(.Rows.Count, 1).End(xlUp).Offset( _
1, 0).Resize(1, rCopy.Columns.Count).Value = _
rCopy.Value
End With
wbTwo.Sheets(1).Cells(1, 1).Resize( _
1, rCopy.Columns.Count).Value = rCopy.Value
rCopy.ClearContents
Next i
wbTwo.Close SaveChanges:=True
Else
MsgBox Application.Substitute(sERR, "$$", sPATH & sFILE2)
End If
wbOne.Close SaveChanges:=True
Else
MsgBox Application.Substitute(sERR, "$$", sPATH & sFILE2)
End If
End Sub

Private Sub OpenFIle(sFileName As String, _
Optional sFilePath As String)
Dim wbTemp As Workbook
On Error Resume Next
Set wbTemp = Workbooks(sFileName)
If wbTemp Is Nothing Then _
Workbooks.Open Filename:=sFilePath & sFileName
On Error GoTo 0
End Sub


In article ,
(Paul Marshall) wrote:

Fallen at the first hurdle!

Tried to run the code but couldn't get it past the first few lines. I
think something to do with what I entered as 'my path' as I get an
error message saying error opening file. Any hints on what I should be
entering here?

I would like to take this up one level (oh know I hear you cry!) and
use multiple sheets on the data entry file to update to mulitple
sheets on the aerial survey file. How would the code change for this
purpose.

Would it be simpler for me to email the workbooks for you (somebody)
to look at?

I really apprecaite any help with this - it has been driving me mad
for over a week!

Best wishes

Paul