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.
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.