Linking multiple excel workbooks and gathering data in one single sheet

Hi,

I am new to working with VBA and I'm probably trying to do  something above my level of knowledge.
I want to link multiple Excel Workbooks from one folder and gather specific cells (data) in one single sheet.

I got the following code working, but it only gives the output of all files in one workbook, with the value of cells A4: C4.

Code:

Sub MergeAllWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim NRow As Long
Dim FileName As String
Dim WorkBk As Workbook
Dim SourceRange As Range
Dim DestRange As Range


' Create a new workbook and set a variable to the first sheet.
Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)


' Modify this folder path to point to the files you want to use.
FolderPath = "C:\Users\603964\Desktop\Test"


' NRow keeps track of where to insert new rows in the destination workbook.
NRow = 1


' Call Dir the first time, pointing it to all Excel files in the folder path.
FileName = Dir(FolderPath & "*.xl*")


' Loop until Dir returns an empty string.
Do While FileName <> ""
' Open a workbook in the folder
Set WorkBk = Workbooks.Open(FolderPath & FileName)


' Set the cell in column A to be the file name.
SummarySheet.Range("A" & NRow).Value = FileName


' Set the source range to be A9 through C9.
' Modify this range for your workbooks.
' It can span multiple rows.
Set SourceRange = WorkBk.Worksheets(1).Range("A4:C4")


' Set the destination range to start at column B and
' be the same size as the source range.
Set DestRange = SummarySheet.Range("B" & NRow)
Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
SourceRange.Columns.Count)


' Copy over the values from the source to the destination.
DestRange.Value = SourceRange.Value


' Increase NRow so that we know where to copy data next.
NRow = NRow + DestRange.Rows.Count


' Close the source workbook without saving changes.
WorkBk.Close savechanges:=False


' Use Dir to get the next file name.
FileName = Dir()
Loop


' Call AutoFit on the destination sheet so that all
' data is readable.
SummarySheet.Columns.AutoFit
End Sub

now I want to select multiple cells from the files that are in the folder, but here I get stuck.
unfortunately I can not manage to select multiple cells and get them in a format.

The output needs te be something like this:

      A                 B      C     D      E      F      G     H     I       J
1 Filename 1      A9      B9      C9      A10      B10      C10      A42      B42      C42
2 Filename 2      A9      B9      C9      A10      B10      C10      A42      B42      C42
3 Filename 3      A9      B9      C9      A10      B10      C10      A42      B42      C42
4 Filename 4      A9      B9      C9      A10      B10      C10      A42      B42      C42
5 Filename 5      A9      B9      C9      A10      B10      C10      A42      B42      C42
6 Filename 6      A9      B9      C9      A10      B10      C10      A42      B42      C42
7 Filename 7      A9      B9      C9      A10      B10      C10      A42      B42      C42
8 Filename 8      A9      B9      C9      A10      B10      C10      A42      B42      C42
......etc.

Could someone please help me with my challenge?