Copy a formula result to a cell in another sheet + add succesive formula results in consecutive rows

Copper Contributor



I am running a partially randomize set of data and trying to find the best solutions depending on certain parameter changes. I need to "record" certain solutions and then compare different results for different parameters each time the randomized variables are recalculated.


I would like to do the to following:


  1. On Sheet1, cell S255, is the result of a formula =SUM(M252:S252)

  2. I need to automatically add that result (Sheet1 S255), to Sheet5, column A, starting at A1.

  3. Then, each time the formula is recalculated and the result changes, I need the new result to be added to the consecutive row to the previous result (so the second result would go to A2, third one to A3, and so on).

Looking for similar cases I have come to be able to do #1 and #2 using this event:

Private Sub Worksheet_Change(ByVal Target As Range) 
Dim ws As Worksheet: Set ws = Sheets("Sheet5") 'declare and set the worksheet the data is to be copied into, amend the sheet name as required
If Target.Address = "$S$255" Then 'if anything changes in C6 or C9 in this sheet
   ws.Range("A1").Value = Target.Parent.Range("S255") 'copy the value from cell C10 in this sheet to Sheet2 in cell E5
End If
End Sub

Doing #3 is proving more challenging. What event would be suitable to do so?


Thanks in advance for your time and understanding!

5 Replies


Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet5")
    If Not Intersect(Range("M252:S252"), Target) Is Nothing Then
        ws.Range("A" & ws.Rows.Count).End(xlUp).Offset(1).Value = Range("S255").Value
    End If
End Sub

Thank@Hans Vogelaar 

For some reason, it is not working though. I keep getting the "Compile error: Method or data member not found" message.


The code works as intended when I test it.

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.

Thanks @Hans Vogelaar ! Went for the second option.


You copied the code into a standard module.

It should be copied into the worksheet module of Sheet1 instead. To activate this module, right-click the sheet tab of Sheet1 and select 'View Code' from the context menu.

See your private messages.