Forum Discussion
record data from a specific cell at the same time once per day?
The problem was that the code used different worksheet names.
I have given cell Z129 on Sheet1 the name CurrenPercentage. If you insert or delete rows above it, or columns to the left of it, Excel will automatically adjust the definition of the name. The code now uses the name instead of the literal address Z129.
(I took the liberty to change some date formulas on Sheet1 to hide errors)
HansVogelaar I think if it updated when you turned on the computer or opened the excel workbook and it updated then that would be awesome. I'd prefer not to have to click a button but everyday to update it. It also doesn't have to be an exact time but just used midnight as an example as I didn't want data collected everytime the cell changes percent availability as this would be maybe 10 times per day but rather once per day, a snapshot to compare one day to the next. It would be ideal if it were the same time everyday, and perhaps midnight so at days end whatever that percentage available would be the snapshot of the day and start the new day.
See the attached sample workbook. It is a macro-enabled workbook, so you will have to allow macros when you open it.
The first time it is opened on a day, the Workbook_Open event procedure in the ThisWorkbook module will enter the previous day in column A in the next available row of Sheet 3, and the current value of Z129 (which should be the value it was at the end of the previous day) on Sheet 1 in column B.
If the workbook is reopened later on the same day, nothing will happen.
- Hi there. Just wanna ask on How to make it record the latest update on the same day. Like for example in the morning it was 65% then became 87% in the afternoon. Your vba i try to use. Thank u
Could you attach a new version of the workbook with some dummy data that shows how the time of day comes into it?
And do you want to show only the latest update of each day, or all intermediate values?
- Your vba is working. It takes data from the previous day. Thats all i need. Thank you!
HansVogelaar As soon as I opened your sample, it did exactly as you mentioned! You can't imagine how much time I spent trying to figure this out. Your sample is exactly what I was looking for. I spent much of the day learning by watching videos online to understand macros and what you were talking about... I'm still learning. So I tried to type in the exact macro as you created but it won't work on my spreadsheet and I don't know what I'm doing wrong. I attached it and if you are willing to please take a look and see what I'm doing wrong?
Also I just have two more questions please. If I deleted a column from sheet 1 or added, would the macro automatically adapt like cells on the spreadsheet will? For example if I had 5 columns and formulas in column 4 that used data from column 2 but deleted column 1, 2 becomes one and the spreadsheet automatically changes all the formulas accordingly. Also, the same if I deleted a couple of rows so that the percentage is no longer "z129" but say "z127" would the macro adjust or would I have to edit the macro myself? Thank-you very much!
The problem was that the code used different worksheet names.
I have given cell Z129 on Sheet1 the name CurrenPercentage. If you insert or delete rows above it, or columns to the left of it, Excel will automatically adjust the definition of the name. The code now uses the name instead of the literal address Z129.
(I took the liberty to change some date formulas on Sheet1 to hide errors)
Hans, I plotted a line graph on sheet 3 to show the data of the specific date with that day's availability. I had to manually do this. Is there way to have the graph update automatically when the spreadsheet updates itself in the columns A and B of sheet? Is this done with a macro also?
I attached the spreadsheet as fresh so I can start from scratch and move forward nice and neat.
