Forum Discussion

simonnw1025's avatar
simonnw1025
Copper Contributor
Aug 26, 2024

Automated Date Stamping Formula

Hello

 

I have a spreadsheet for finances and I want a formula that takes a total from another sheet every day and records it. Maybe at 9am.  It then won't change.

 

Currently I have to go to copy and paste values but I have to do it daily.

 

Any suggestions or am I hoping too much?

excel

3 Replies

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Aug 26, 2024
    Which version of Excel are you using and on what platform? E.g. is this Desktop Excel or Excel on-line? Is it on Windows or on a Mac (or on your mobile)?
      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        Aug 26, 2024

        simonnw1025 Assuming your finances sheet is called "Finances" and the cell you are taking the total from is on a sheet called "TotalSheet", cell B75. Further assuming you want to list the historical values in columns Y (date) and Z (total).

        This macro should do the trick:

        Sub StoreTotal()
    Dim ttl As Variant
    ttl = ThisWorkbook.Worksheets("Finances").Range("B75").Value
    With ThisWorkbook.Worksheets("TotalSheet")
        'See if we need to add a heading
        If .Range("Y1").Value = "" Then
            'no heading, add one
            .Range("Y1").Value = "Date"
            .Range("Z1").Value = "Total"
        End If
        'find the first empty row in column Y
        With .Range("Y" & .Rows.Count).End(xlUp).Offset(1)
            .Value = Date
            .Offset(, 1).Value = ttl
        End With
    End With
End Sub

         

Resources