How to import a range of cells from a different worksheet

Hello,

Please help me to import a range of cells depend on the output cells generated on different worksheets on a page-2(in my folder the different file names are C1, C2, C3, C4,C5 & output is on page-2-(i can rename it as Output-1 to Output-5 if vba requires different page names))

I have name range input in current worksheet which i use as reference to get result on C1, C2, C3, C4, C5 worksheets saved in a directory(C:\Users\MyComp\Desktop\Cables).
Conditions are there are 2 types of results.. it is either range A1:G24 or A1 to N24.(this result was generated from name range input)

i want to import these outputs either using the name ranges or using the result on page-2 of worksheets C1 to C5 .

Output should begin in current workbook, page-4 (renamed as Combined) from Cell A1 to the resultant range i.e from file C1, next continued to the next cell
till there is name range input in current sheet assume it is on page1 R1:R5.

Please help me to solve the problem.. if any alternate method is there please inform..

Sub dir()
Sheets("FROM DIRECTORY, worksheets C1 TO C5").Select

from this worksheet
For Each cell In Range("R1:R5")

    Application.Goto Reference:=cell.Value ( ("FROM DIRECTORY, worksheets C1 TO C5").for R1 VALUE (NAME RANGE) find the name range in  C1, R2 VALUE(NAME RANGE) find the  name range in C2...TILL R5 TO C5)
    If Sheets("COMBINED").Range("A1") = vbNullString Then
        Selection.Copy Output range to ("COMBINED").Range("A1")
    Else
        Selection.Output Range("COMBINED").Cells(A1, Columns.Count).End(xlToLeft).Offset(0, 1)
    End If
Next cell

End Sub