Is it possible to import namerange(values) from other pages

mathewmunna Posts 27 Registration date Tuesday November 18, 2014 Status Member Last seen May 21, 2015 - May 13, 2015 at 08:57 AM

i have 10 number of pages which has name like file1.. file2.. file3 to file 10
i have defined name range in all files..
i have another workbook where i want to import a name range (values with format as it looks in that file) which i defined in page1 cell a1:a10 & i need output in page2 exactly like bellow code, but i need these name range from cell a1 is from file1, a2 from file2, a3 from a10 from file10... till the value is there in range a1:a10. please help me regarding this..

Sub RunMe()
Workbook(file1, file2, file3, file4..file10)

For Each cell In Range("A1:A10")
Application.Goto Reference:=cell.Value

Workbook(Currnet file)
If Sheets("Page2").Range("A1") = vbNullString Then
Selection.Copy Sheets("Page2").Range("A1")
Selection.Copy Sheets("Page2").Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
End If
Next cell

End Sub