Get cell contents from closed workbook using yesterdays date

  • I do a daily report and on ti i have created a button that saves the file using todays date to a location on a network drive .

    Private Sub CommandButton10_Click()

    ActiveWorkbook.SaveAs ("N:\...\Daily Reads\Report_Day_" & Format(Now(), "YYYY-MM-DD") & ".xlsm")

    End Sub

    that works fine what I would like to do is when i create a new workbook tommorow i want it to get a couple of figures from yesterdays report so that i can do a calculation

    e.g we have a figure for total water use so yesterday may be 8796 litres and todays may be 9321 litres , I would like to get yeterdays figure subtract it from todays and get a daily usage.

    I have tried

    ='("N:\...\Daily Reads\[Report_Day_" & Format(Now(), "YYYY-MM-DD")-1] & ".xlsm")Plant Daily'!$c$35

    but it throws a ref error.

    thanks in advance

  • Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

    How to use code tags

    Just highlight all of the code and press the <> in the post menu above button to add the code tags.


  • Roy

    Yes when i save the active workbook it saves the figure that i entered , what i want to do is when i open a new workbook the following day I want to retrieve the figure from the workbook of day before. for example on monday 2nd I created a file called Report_Day_2021_08_02 within that workbook is a cell for the total water , the next day i create a new workbook called Report_Day_2021_08_03 which has the new total water figure, at the moment i have to open yesterdays workbook , write the water figure down and then open todays workbook and get todays figure then calculate the daily increase. I am hoping to find a way to get yesterdays figure so that the daily figure can be automatically calculated.


  • Could you attach copies of your two files? It would be easier to see how your data is organized and to test possible solutions. Include a detailed explanation of what you want to do using a few examples from your data and referring to specific cells, rows, columns and sheets. De-sensitize the data if necessary.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.