Referencing a dynamic total from another worksheet

[Edski]

Hi,

Apologies if this has already been covered in a previous thread, but I haven't been able to locate anything on this or other websites (probably poor search criteria on my part).

I am trying to populate a total from another worksheet, but although the value of the referenced worksheet is returned it is doesn't appear as a formula in the cell.

E.g., if cell 'K1' in worksheet 'MMM' shows a total of 5,000, in cell 'C2' in the worksheet 'Open Balances' I would expect to see the result 5,000 in 'C2' with the formula bar for 'C2' showing as '=MMM!K1'.
However, using the the following code it returns the result, but the formula bar doesn't show the formula and therefore cell 'C2' would not update as the total in worksheet 'MMM" changes.

This is what I was expecting:

Relevant part of the code:
Dim str As String

Sheets("Open Balances").Select

Cells.Find(What:=str, after:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Offset(0, 2).Value = Worksheets(str).Range("K1")

This is what occurs when the macro is run:

I need cell 'C2' to update as the total changes in the 'MMM' worksheet.

Any help would be greatly appreciated.

Thanks & regards.

 

[Fluff]

Hi & welcome to MrExcel.
How about

   ActiveCell.Offset(,2).Formula = "='" & Str & "'!K1"

 

[Edski]

Hi & welcome to MrExcel.
How about

   ActiveCell.Offset(,2).Formula = "='" & Str & "'!K1"

Thanks Fluff, that's awesome. It works perfectly.

Much appreciated.

Cheers,
Edski

 

[Fluff]

You're welcome & thanks for the feedback.