CODEWITHSUNDEEP
excelexcel-formulavba
user1044585
user1044585

Reputation: 521

Excel - Keep calculation results as new rows on a separate sheet

I have quite a complicated workbook with not so straight forward calculations using distribution averages. At the end of the workbook is a resultant value in a particular cell. Every time the work book re-calculates that value changes slightly. Is there a way I can save all these values every time I re-calculate to a new cell? Ideally I would like to do this 100+ times to be able to plot a graph against the possible values and get an average.

Would a Macro be able to do this job? If so... where do I start?

Thanks!

Upvotes: 0

Views: 982

Answers (1)

basodre
basodre

Reputation: 5770

You'd have to subscribe to the Workbook Calculate event. In order to do so, open the VBE by pressing ALT + F11. On the left side, you should see the Project Explorer tree view (if not, press CTRL + R). Right click on ThisWorkbook, then select View Code. This opens up a text editor window. At the top of this window, there are two dropdown boxes. In the left dropdown, select Workbook. In the right dropdown, select SheetCalculate.

Now, the workbook will perform an action at each calculation. Below, I have a sample script. There is a formula in Sheet2, cell A1 for which we want the history. See if you can adapt the code to your needs, or reach out with more information if your need it.

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Dim sData As Worksheet
    Dim nNewRow As Long

    Set sData = Worksheets("Sheet2")
    nNewRow = sData.Cells(Rows.Count, 1).End(xlUp).Row + 1

    sData.Cells(nNewRow, 1).Value = sData.Range("A1").Value

End Sub

Upvotes: 1

Related Questions