Timestamp data daily

Rexy69

New Member
Joined
Apr 28, 2015
Messages
16
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

Well-known Member
Joined
Apr 11, 2012
Messages
8,543
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.
Code:
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

New Member
Joined
Apr 28, 2015
Messages
16
that looks great will try it when I get home and let you know
Thanks for the quick response
 

Rexy69

New Member
Joined
Apr 28, 2015
Messages
16
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)

Code:
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
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,533
Office Version
365, 2010
Platform
Windows, Mobile
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

Code:
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

New Member
Joined
Apr 28, 2015
Messages
16
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

Code:
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
 

Forum statistics

Threads
1,082,587
Messages
5,366,484
Members
400,894
Latest member
frog9000

Some videos you may like

This Week's Hot Topics

Top