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

Copper Contributor

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

Hi,

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?

5 Replies

# Re: Copy a formula result to a cell in another sheet + add succesive formula results in consecutive

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

# Re: Copy a formula result to a cell in another sheet + add succesive formula results in consecutive

Thank@Hans Vogelaar

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

# Re: Copy a formula result to a cell in another sheet + add succesive formula results in consecutive

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.

# Re: Copy a formula result to a cell in another sheet + add succesive formula results in consecutive

Thanks @Hans Vogelaar ! Went for the second option.

# Re: Copy a formula result to a cell in another sheet + add succesive formula results in consecutive

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.