Runtime Error 1004 cant find the file, but it knows it's name

Have you tried using the full path?

No, I mean replace 'C:\Users\Paul\Documents\........\Completed Surveys\' with the full path, specifically the '........' part, which I don't even think is a valid folder name.

try this way:

Sub CopyPropertyDetails()
Dim myfile As String
Dim erow, path As String
path = "C:\Users\Paul\Documents\Business\2016\1 January\National Trust\Completed Surveys\"
myfile = Dir(path)

Do While Len(myfile) > 0
If myfile = "ZCollation Tool v1.xlsm" Then
Exit Sub
End If

Workbooks.Open (path & myfile)
Range("B4:B9").Copy
[.....]

Right, didn't realise that.

What you need to do then is include the full path when opening the file.

Option Explicit

Sub CopyPropertyDetails()
Dim myfile As String
Dim strPath As String
Dim erow As Long

    strPath = "C:\Users\Paul\Documents\........\Completed Surveys\"
    myfile = Dir(strPath)

    Do While Len(myfile) > 0

        If myfile = "ZCollation Tool v1.xlsm" Then
            Exit Sub
        End If

        Workbooks.Open (strPath & myfile)
        Range("B4:B9").Copy
        ActiveWorkbook.Close

        erow = CollectionData.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        ActiveSheet.Transpose Destination:=Worksheets("CollectionData").Range(Cells(erow, 1), Cells(erow, 6))

        myfile = Dir

    Loop

End Sub

carefull with that Range("B4:B9").Copy there.. it is not fully referred and it will NOT copy from the new opened file... I would do it this way:

Sub CopyPropertyDetails()
    Dim myfile As String
    Dim erow, path As String, nextF As Workbook
    Dim wbZC As Workbook, wsZC As Worksheet
    
    Set wbZC = ActiveWorkbook
    Set wsZC = wbZC.Worksheets("CollectionData")
    
    path = "C:\Users\Paul\Documents\Business\2016\1 January\National Trust\Completed Surveys\"
    myfile = Dir(path)
    Application.ScreenUpdating = False
    Do While Len(myfile) > 0
        If myfile = "ZCollation Tool v1.xlsm" Then
            Exit Sub
        End If
        
        Set nextF = Workbooks.Open(path & myfile)
        nextF.Sheets(1).Range("B4:B9").Copy            ''pls modify this line if needed 
        With wsZC
            erow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
            .Range("A" & erow).PasteSpecial Transpose:=True
        End With
        nextF.Close
        myfile = Dir
    Loop
    Application.ScreenUpdating = True
    
End Sub

Thanks guys, that's worked.
One of the cells that it is copying is based on a drop down, so gives a pop up for every file that it opens saying that it already exists "click yes to use that version of the name..."
Is there a way of making stopping this would a transpose of the value work?

How is the popup being generated?

Is it via data validation, code or something else?