Copying dynamic named ranges to different workbooks

[getafix40]

Hi

I need to copy and paste a formula (highlighted in bold) to all the .xlsm files in a specific directory. The process works but I am getting a #NAME? error.

The dynamic name ranges refer to a workbook called Figure the deal2.xslm

Private Sub CommandButton1_Click()


MyDir = ActiveWorkbook.Path
DataDir = MyDir & "\Job Cards\"
ChDir (DataDir)
Nextfile = Dir("*.xlsm")
While Nextfile <> ""
Workbooks.Open (Nextfile), WriteResPassword:="*********"
Workbooks(Nextfile).Sheets("Sheet1").Range("c5") = "=VLOOKUP($I$4,Table,MATCH('[Figure the deal2.xlsm]StockSheet'!B$1,ColumnLabel,0),FALSE)"
Workbooks(Nextfile).Save
Workbooks(Nextfile).Close
Nextfile = Dir()
Wend

End Sub

Any ideas how to get it to work?

 

[adam087]

It would throw a #NAME error if either Table or ColumnLabel were not defined names in the workbook.
There may be other explanations that I've missed.

/AJ

 

[getafix40]

It would throw a #NAME error if either Table or ColumnLabel were not defined names in the workbook.
There may be other explanations that I've missed.

/AJ

I thought the same as the named ranges refer to a different work book (figure the deal2.xlsm) How do I copy the named ranges to each file (workbook)

I created a template file with all the correct named ranges and when I save file saves as it works.

The above script will go through all the files and make the necessary changes automatically - will save me loads of time.