Net worth updating doesn't work using VBA

[mSolver]

in excel, I have a portfolio tracker with a cell that tabulates my total net worth. I would like this total number to be transposed to another sheet automatically with the date every time I open the excel file, only if the new net worth is more than the previous net worth on a daily basis.

This is to create a performance snapshot, so I can plot a graph later on.

Based on the code below, nothing is being written to History though. Why is that so?

It is most definitely a xlsm with macro enabled already.

Private Sub Workbook_Open()
    Dim sourceSheet As Worksheet
    Dim historySheet As Worksheet
    Dim lastRow As Long
    Dim currentDate As Date
    Dim currentNetWorth As Double
    Dim previousNetWorth As Double

    ' Set the source sheet (where the total net worth is calculated)
    Set sourceSheet = ThisWorkbook.Sheets("Portfolio Tracker")

    ' Set the history sheet (where the data will be transposed)
    Set historySheet = ThisWorkbook.Sheets("History")

    ' Find the last used row in the history sheet
    lastRow = historySheet.Cells(historySheet.Rows.Count, "A").End(xlUp).Row

    ' Get the date and total net worth values
    currentDate = Date
    currentNetWorth = sourceSheet.Range("B2").Value  ' Change "B2" to the cell address of your total net worth

    ' Check if the new net worth is greater than the previous day's net worth
    If lastRow > 1 Then
        previousNetWorth = historySheet.Cells(lastRow, "B").Value
        If currentNetWorth <= previousNetWorth Then
            Exit Sub ' Exit the macro if net worth hasn't increased
        End If
    End If

    ' If there's an increase in net worth, update the history sheet
    lastRow = lastRow + 1
    historySheet.Cells(lastRow, "A").Value = currentDate
    historySheet.Cells(lastRow, "B").Value = currentNetWorth
End Sub

 

[mSolver]

bumping up the post.

 

[Peter_SSs]

Excel does not let me name a worksheet as History:

If I name the worksheet myHistory and change this single line in your code, then the code works for me

Set historySheet = ThisWorkbook.Sheets("myHistory")