Set Range for Dynamic Array from other workbook

[StijnRoelen]

Hi all,

I'm a bit of a novice to VBA so I'm trying my best to explain my goal but excuse me if the explanation is a bit unclear

I'm trying to reference a range of cells in another workbook with a dynamic array like so:

With ActiveSheet
   .Cells(12, 14).Formula2R1C1 = _
        "='[workbook.xlsm]Sheet1'!R14C5:R15C5"
End With

Except, i want the size of the range to correspond with a number in a different cell in the active workbook.
For example the "amount of rows" cell says 4, I need the referenced range from the external workbook to be R14C5:R17C5 in Cell R12C14 in the active workbook and spill 3 rows over

Thanks in advance!

 

[StijnRoelen]

By the way, eventually I'm going to run this for multiple external worbooks so they will be closed