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:

Sub getworkbook()
    ' Get workbook...
    Dim ws As Worksheet
    Dim filter As String
    Dim targetWorkbook As Workbook, wb As Workbook
    Dim Ret As Variant

    Set targetWorkbook = Application.ActiveWorkbook

    ' get the customer workbook
    filter = "Text files (*.xls),*.xls"
    Caption = "Please Select an input file "
    Ret = Application.GetOpenFilename(filter, , Caption)

    If Ret = False Then Exit Sub

    Set wb = Workbooks.Open(Ret)

    wb.Sheets(1).Move After:=targetWorkbook.Sheets(targetWorkbook.Sheets.Count)

    ActiveSheet.Name = "ARCHIVE"
End Sub

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

Formula:

=ARCHIVE!C7

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

Formula:

=IF(ISERROR(ARCHIVE!C7),"",ARCHIVE!C7)

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

Range("C7").Value = Sheets("Archive").Range("C7").Value

code tags added

code tags added

Anyone...?