Creating a script to paste in data from series of spreadsheets into one

Are all the the workbooks to copy from named Questionnaire...?

No, some will likely have different names, though they'll all be identical otherwise.

So the only workbooks in the folder will be the results workbook and the questionnaire workbooks?

Correct

Try this.

Option Explicit

Sub ConsolidateResults()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim rngDst As Range
Dim strFileName As String
Dim strPath As String

    Application.ScreenUpdating = False
    
    strPath = "C:\Test\Questionnaires\"    ' change to folder that holds the results and questionnaire workbooks

    strFileName = Dir(strPath & "Results*.xlsx")

    If Len(strFileName) = 0 Then
        MsgBox "Results workbook could not be found, stopping execution!", vbInformation, "Results file not found"
    Else
        Set wbDst = Workbooks.Open(strPath & strFileName)
        Set rngDst = wbDst.Sheets("Input Data").Range("F5")
    End If

    strFileName = Dir(strPath & "*.xlsx")

    Do

        If strFileName <> wbDst.Name Then
            Set wbSrc = Workbooks.Open(strPath & strFileName)
        End If
        
        If Not wbSrc Is Nothing Then
        
            wbSrc.Sheets("Copy").Range("F1:F40").Copy rngDst

            wbSrc.Close SaveChanges:=False
            
            Set wbSrc = Nothing
            
            Set rngDst = rngDst.Offset(, 1)
            
        End If

        strFileName = Dir()

    Loop Until Len(strFileName) = 0

    Application.ScreenUpdating = True
    
End Sub

Select allOpen in new window

Thanks Norie, I've put that into a Macro, called 'ConsolidateResults' in the Results Workbook.
I hope I did that bit right.  I've put that workbook and one Questionnaire workbook in a folder.  I am testing this on a Windows PC.

When I run the macro, I get the message: "Results Workbook could not be found" then a Runtime error 91

Is it me?

I put the code in it's own workbook rather than the 'results' workbook as I assumed that's what you wanted.

Having the code in the 'results' workbook itself would make things far easier, for one thing we wouldn't need to try and find that workbook.

If the code was in the 'results' workbook it would simply be a matter of looping through the workbooks in the folder with the questionnaire results.

Thanks so much for this Norie
If it's possible to have the macro in the Reports workbook that would be even easier for me.  Don't worry if it's a hassle for you.  

Looks like we're getting there, but I'm still getting that error.  I've now created a folder with Results.xlsx in it, the Macro spreadsheet, which I've called Macro.xlsm and a test Questionnaire5.xlsx, which I assume could have any name?

I've changed line 12 in which I need to change the folder location (hadn't spotted that first time).  But I'm still getting the same error.

Also, is there an alternative way of finding the other files, like just to look in the workbook's own folder, so that if I send this to someone so they could use it on their machine, it'll run without knowing the folder location?  I'm not sure how to get the filepath off a mac anyway.

You are an absolute star!  That works fantastically.  I have yet to test this on a Mac but it works great on my PC.  I just copied the script into my original Results file and it worked superbly.  Copied the text into the correct tab.  
One question.  Is there an instruction to paste the data without any formatting? (I quite understand if you want to move on to help others - I can live with this.  You have exceeded my hopes massively!)
Thank you so much.

Fantastic work