PasteSpecial xlPasteAll

is the same as a straight copy and paste, so you might as well just do that:
(not sure why you changed it? )

However,

For Each c In Range("B4:B42")

the Range("B4:B42") refers to the activeworksheet which would be in the
SourceFile. Thus c would be in the sourcefile and your destination is
specified relative to c. Perhaps this is the source of your problem.
Perhaps you need

for each c in DestFile.worksheets("Sheet1").Range("B4:B42")


Sub copyProjectDates()
Dim sourceFile As Workbook
Dim DestFile As Workbook
Dim copyStartCell As Range
Dim copyEndCell As Range
Dim copyRange As Range
Dim destRange As Range
Dim destStartCell As Range
Dim destEndCell As Range
Dim foundCell As Range
Dim c As Range
Dim searchData As String
Dim foundAddress As String
Dim foundRow As String

'speed things up a bit
Application.ScreenUpdating = False

Set DestFile = workbooks("Destination.xls")

'Set sourceFile = GetObject("C:\1-Joanne\Excel\Projects
'Overview.xls")
set SourceFile = Workbooks.Open( filename:= _
"C:\1-Joanne\Excel\Projects Overview.xls")
DestFile.worksheets("Sheet1").Range("B4:B42")
If c < "" Then
searchData = c.Value
With sourceFile.Sheets("Sheet1")
Set foundCell = .Cells.Find(What:=searchData, _
After:=.Cells(1,1), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not foundCell Is Nothing Then 'project is found
set copyStartCell = foundCell.Offset(0, 10)
set copyEndCell = foundCell.Offset(0, 15)
set copyRange = Range(copyStartCell,copyEndCell)
set destStartCell = c.Offset(0, 12)
set destEndCell = c.Offset(0, 17)
set destRange = Range(destStartcell,destEndCell)
c.Offset(0, -1).Value = foundRow
foundCell.Interior.Color = vbRed
copyRange.Copy Destination:=destRange ' <== Added code
Else 'project not found
c.Interior.Pattern = xlPatternLightHorizontal
End If
End With
End If
Next
'sourceFile.Close SaveChanges:=True
End Sub

--
Regards,
Tom Ogilvy



"jowatkins" wrote in message
...
OK, sorry, I failed to see the other changes you had made. I've now
applied all the changes and the program apears to run smoothly.
However, when it is carrying out the copy and paste, the data is being
pasted into the sourceFile workbook, rather than the destFile.

I've tried doing it a bit differently, eg.

change

copyRange.Copy destRange

To.

copyRange.Copy destFile.Sheet1.destRange

And

copyRange.Select
Selection.Copy
destFile.Activate
Sheet1.Range(destStartCell, destEndCell).PasteSpecial xlPasteAll

but to no avail.


---
Message posted from http://www.ExcelForum.com/