Populating a Named Range & Cell Array

[timbrind]

I have a source and destination workbook, namely mysource & mydest.
An administrator will control mysource whilst up to 40 users can edit mydest.
Both workbooks are essentially the same, with mydest having live links back to mysource and vice versa.

Upon opening mydest, a macro imports the mysource data. It selects & copies a named range, activates mydest and pastes it to the correct cell:

[FONT=Courier New]Sub importsource()[/FONT]
[FONT=Courier New] [/FONT]
[FONT=Courier New]'select first worksheet[/FONT]
[FONT=Courier New]Windows(mysource).Activate[/FONT]
[FONT=Courier New]Sheets(mm1).Select[/FONT]
[FONT=Courier New] [/FONT]
[FONT=Courier New]'begin import[/FONT]
[FONT=Courier New]Windows(mysource).Activate[/FONT]
[FONT=Courier New]Application.Goto Reference:="pa"[/FONT]
[FONT=Courier New]Selection.Copy[/FONT]
[FONT=Courier New]Windows(mydest).Activate[/FONT]
[FONT=Courier New]Range("A3").Select[/FONT]
[FONT=Courier New]ActiveSheet.Paste Link:=True[/FONT]
[FONT=Courier New] [/FONT]
[FONT=Courier New]Windows(mysource).Activate[/FONT]
[FONT=Courier New]Application.Goto Reference:="tn"[/FONT]
[FONT=Courier New]Application.CutCopyMode = False[/FONT]
[FONT=Courier New]Selection.Copy[/FONT]
[FONT=Courier New]Windows(mydest).Activate[/FONT]
[FONT=Courier New]Range("B3").Select[/FONT]
[FONT=Courier New]ActiveSheet.Paste Link:=True[/FONT]
[FONT=Courier New] [/FONT]
[FONT=Courier New]...etc[/FONT]
[FONT=Courier New] [/FONT]
[FONT=Courier New]End Sub[/FONT]

There are many worksheets with many named ranges (the above being just a snippet). I'd like to dynamically populate the hard coded Named Ranges and destination cells. I have a macro that collects named ranges:

Sub returnRangeNames() Set rangeNames = ActiveWorkbook.Names
For r = 1 To rangeNames.Count
Ranges = (rangeNames(r).Name)
MsgBox (Ranges)
Next r End Sub

So my aim is to collect each Named Range and its Address (first cell). I'm struggling to collect the Address of the Named Range and then construct an array of values to use with the importsource macro.
I'm not sure if that's the right approach, but any help would be much appreciated.