Timestamp data daily

Rexy69

New Member
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
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
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

Well-known Member
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
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
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top