There is a method much easier and better than VBA if the Excel 2010 version contains the right option ...
I've not used this with Excel 2010 but PowerQuery should be available as a free addin from Microsoft. If the 2010 addin version is anything like the current version of PowerQuery, you can easily create a query that loads all or some files from a folder and merges them.
PowerQuery is a point and click ETL (Extract, Transform and Load) tool for Excel and is both (fairly) easy to use and extremely powerful.
To do this in VBA is a lot more complex.
However, Microsoft provide some example code that might be used as a starting point
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\Peter\invoices\"
' 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("A9:C9")
' 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, _
' 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.
' Use Dir to get the next file name.
FileName = Dir()
' Call AutoFit on the destination sheet so that all
' data is readable.
See here for the full information.
There is also an example already on Stack: