Fixing an Excel macro to copy data from a number of workbooks into another one.

I've created a questionnaire in Excel for team members to complete, that is then assembled into an Excel report.  To pull the data from each of the questionnaires and create the report I've had someone create a macro for me.  However, I realised it doesn't work quite as intended and haven't been able to contact the macro author.  The macro is meant to copy a range of cells (always F1:F40 on the "Copy" sheet) in a set of 'source' workbooks and paste them in, next to each other on the "Input Sheet" of a 'target' workbook, starting at F5, then G5, H5 etc.  The macro is run from the target workbook.  All the source workbooks will be held in the same folder as the target report workbook, so the macro opens every other wookbook in the folder, copies the data, closes it, and moves on to the next workbook to copy that one, and so on.
The macro almost works but has two issues:
It seems to copy the range of cells on the first workbook correctly, but then copies the next column to the right (G1:G40) which is blank) from the next workbook, rather than the same range F1:F40 .  This is probably because it also has to paste into the next column the right in the target workbook.
The second problem is that the macro is pasting the formulas rather than the values from the cells and after pasting some of them appear to be referencing the wrong cells, though I'm not sure why.  Is it possible to have values rather than formulas pasted?

Could anyone shed some light on what is going wrong and how to fix it?  I have no clue how to write a macro.


Here's the macro:
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 = ThisWorkbook.Path & Application.PathSeparator
    Set wbDst = ThisWorkbook
    Set rngDst = wbDst.Sheets("Input Data").Range("F5")

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

    Do

        Set wbSrc = Workbooks.Open(strPath & Application.PathSeparator & strFileName)

        wbSrc.Sheets("Copy").Range("F1:F40").Copy rngDst

        wbSrc.Close SaveChanges:=False

        Set wbSrc = Nothing

        Set rngDst = rngDst.Offset(, 1)

        strFileName = Dir()

    Loop Until Len(strFileName) = 0

    Application.ScreenUpdating = True

End Sub