HELP! Import archive sheet - Simple eh?

Hi Everyone.

I am very new to this so please be gentle.

Problem:
I want to open an archive sheet in a new workbook, and automatically transfer cells from the archive sheet into different cells on a sheet in the new workbook.

I have this VBA code (which works great) to find an archive sheet and open it in the new workbook:

Please Login or Register  to view this content.

I first thought that if the cells on the sheet in the new workbook were referenced:

Formula:

Please Login or Register  to view this content.

the cell would populate with the value of C7 in the archive sheet, but no (sighs).
Then I tried:

Formula:

Please Login or Register  to view this content.

but still no joy.

So to recap;
First I want to put formulas in cells in a sheet in the new workbook to automatically (and instantaneously) acquire the value from the specified cells in the archive sheet (when the archive sheet is loaded/opened).

I may be missing something very basic. It seems like it should be easy but I just can't get Excel to do what I would consider to be quite a simple task.

Any help ASAP would be greatly appreciated.

Thanks

Maybe

Please Login or Register  to view this content.

code tags added

code tags added

Anyone...?