Timestamp data daily

[Rexy69]

Hi, i have a spreadsheet which calculates total profit and there is a cell which has my total profit in, i basically want a sheet in my spreadsheet to read the total profit cell and put a date next to it when the workbook is opened (this may have to be VBA or a macro or something, or maybe its not possible.

i was thinking a vlookup on todays date (Today()) but then the data wont stay when it moves to the next date.

Example of what i am after

when i open the workbook on the 01/01/2019, 02/01/2019, 03/01/2019, 05/01/2019

i want the following to save:
Profit Date
£3000 01/01/2019
£3500 02/01/2019
£3750 03/01/2019
£4100 05/01/2019

can this be done automatically, i have tried searching online but i don't really know what to search so have found nothing similar.

any help would be appreciated

Many Thanks

Rexy~

 

[mumps]

Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. This macro assumes you have a sheet named "Summary". It also assumes that the total profit is in cell A1 of "Sheet1". Change these in the macro to suit your needs. Close the window to return to your sheet. Save the workbook as a macro-enabled file, close it and then re-open it.

Private Sub Workbook_Open()
    Sheets("[COLOR="#FF0000"]Summary[/COLOR]").Cells(Sheets("[COLOR="#FF0000"]Summary[/COLOR]").Rows.Count, "A").End(xlUp).Offset(1, 0) = Sheets("[COLOR="#0000FF"]Sheet1[/COLOR]").Range("[COLOR="#0000FF"]A1[/COLOR]").Value
    Sheets("[COLOR="#FF0000"]Summary[/COLOR]").Cells(Sheets("[COLOR="#FF0000"]Summary[/COLOR]").Rows.Count, "B").End(xlUp).Offset(1, 0) = Date
End

Sub

 

[Rexy69]

that looks great will try it when I get home and let you know
Thanks for the quick response

 

[Rexy69]

Hi sorry for the late reply i am getting an error when i try and open the workbook

"Compile Error :
Syntax Error"

this is the code i used : (i made a seperate sheet called "Test" to try it out on and the profit figure is in the Account Summary sheet in cell c16)

Private Sub Workbook_Open()    
Sheets("Test").Cells(Sheets("Test").Rows.Count, "A").End(xlUp).Offset(1, 0) = Sheets("Account Summary").Range("C16").Value
Sheets("Test").Cells(Sheets("Test").Rows.Count, "B").End(xlUp).Offset(1, 0) = Date
End
Sub

Thanks

Rexy69

 

[MARK858]

Not sure if it is just because you used php tags rather than code tags but all I can see wrong is Sub from End Sub is on the wrong line. Should be

Private Sub Workbook_Open()
Sheets("Test").Cells(Sheets("Test").Rows.Count, "A").End(xlUp).Offset(1, 0) = Sheets("Account Summary").Range("C16").Value
Sheets("Test").Cells(Sheets("Test").Rows.Count, "B").End(xlUp).Offset(1, 0) = Date
End Sub

 

[Rexy69]

Not sure if it is just because you used php tags rather than code tags but all I can see wrong is Sub from End Sub is on the wrong line. Should be

Private Sub Workbook_Open()
Sheets("Test").Cells(Sheets("Test").Rows.Count, "A").End(xlUp).Offset(1, 0) = Sheets("Account Summary").Range("C16").Value
Sheets("Test").Cells(Sheets("Test").Rows.Count, "B").End(xlUp).Offset(1, 0) = Date
End Sub

haha yeah that was it my fault !!

thanks

 

[mumps]

Glad to help.