Pull in data from multiple closed workbooks and append to a single worksheet using VBA

Hi

I have the following code to successfully pull in data from a closed workbook and put that data in a sheet.

My problem is how to amend the code to pull in data from multiple closed workbooks and append each below the other. The code pulls in data from Department1, MasterFile1. There are 10 Departments and 10 MasterFiles. I could simply copy the code 10 times, but feel there must be a more constructive way of doing that.

Any advice will be much appreciated.

Thanks

Sub PullInMasterFile()

'''''''''''''''''''''''''''''''

'Pulls in all data from relavant MasterFile

''''''''''''''''''''''''''''''''

Dim AreaAddress As String

'Clear sheet ready for new data

Sheets("Sheet1").Range("A:AZ").Clear

'Reference the UsedRange Address of Sheet1 in the closed Workbook.

    Sheets("Sheet1").Cells(1, 1) = "= 'N:\Department1\MasterFiles\" & "[MasterFile_1.xlsm]Sheet2'!RC"

    'Pass the area Address to a String

    AreaAddress = Sheets("Sheet1").Cells(1, 1)

    With Sheets("Sheet1").Range(AreaAddress)

            'If the cell in Sheet1 of the closed workbook is not _empty the pull in it's content, else put in an Error.

        .FormulaR1C1 = "=IF('N:\Department1\MasterFiles\" _
        & "[MasterFile_1.xlsm]Summary'!RC="""",NA(),'N:\Department1\\MasterFiles\[MasterFile_1.xlsm]Summary'!RC)"

        'Delete all Error cells

        On Error Resume Next

        .SpecialCells(xlCellTypeFormulas, xlErrors).Clear

        On Error GoTo 0

        'Change all formulas to Values only

        .Value = .Value

    End With

End Sub

Any thoughts on this?