Automatically Save and Copy Values Daily with a Date Stamp

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

What do you want to trigger this update to the Past Closing Values sheet? A button on the sheet? Automatic in some way you can imagine?

If there is already an entry for today and it gets triggered again, what do you want to happen? Delete the old and replace with new? Abort? Allow duplicate date entries?

I think that this is what you want. Probably add a button to run the code.

Please Login or Register  to view this content.

If you add the button then call each macro, I would use the Controls ToolBox button

Please Login or Register  to view this content.

[

Hi Roy,

That explanation works. I was also looking for how to simulate the keystrokes of Control + Alt + F5 but found:

Please Login or Register  to view this content.

Which seems to do the trick. I have created a button that will Refresh the Query, and then transfers the days current closing values. The problem that I am having is that updating the quesry takes some time, and the values are copied before they can be updated. Hopefully that makes sense. I tried putting in a wait command

Please Login or Register  to view this content.

but that does not work. Do you have any idea on how to fix this?




On another note, I have tried to modify your code for the macro I reference above which I call CalculatePercentage. Basically, it takes the values from the previous column to find the percentage change from the day before (example (C4-C3)/C3). I am having some trouble with it, and I would greatly appreciate it if you could let me know what I am doing wrong. I have copied it below. I have also attached a dummy sheet if you dont understand what I am asking.

Please Login or Register  to view this content.

Finally, I am trying to create a table of contents page and I was following the suggestion on your website, by simply hyperlinking the worksheets. I am trying to go for a simplified document and hide the worksheets at the same time. If they are hidden, they cannot be hyperlinked. Is there a setting I missed, or do you have any suggestions on how to do a TOC in the way that I described?

Thanks for your patience and help. I hope I am not asking for too much.

You cannot access a hidden worksheet so hyperlinks will not work. Maybe you could simply hide the tabs themselves from the Tools Menu->Options->View->Sheet tabs.

Your Wait code should delay by 5 seconds is this long enough.

In the CalculatePercentage code your variable is COL,not Cold. If you use Option Explicit you will avoid such errors. I'm not sure what you are doing with it, it's not clear from the example

Hi Roy,

I got the tabs hidden, but I am still having problems with my other two previous points. I will try to clearly explain them.

So this is a financial document used for tracking my stocks. The stocks are in sheets DABBX and DASBX. When I refresh the query (this is the first thing in my macro) , the values update on those sheets.

On the sheet called "holdings" I have the current value of the stocks in the J'th column that are referenced to the DABBX and DASBX sheets. I had a lot of other personal information on that sheet that I have deleted.

I am using the sheet "Daily Closings" to track the closing price of the stock which are in columns B and D. That is what I had you write the initial macro for me. I wanted take that one step further by calculating the percentage change from the previous day and this is done in columns C and D. I have inputed equations to determine these values, but I would like the macro do do it for me.

So to sum this up, the queried stocks are updated, which are reference into the "Holdings" sheet which the macro reads and write the values to Daily Closings.

The problem is that the stock prices DABBX and DASBX are updated but are not updated fast enough to be taken from the macro and written onto "Closing Values" even with a 5 second wait.

Furthermore, my macro to determine the percentage change is not working and I do not know why.

I hope I made this clear. I tried to make my dummy sheet as simple as possible.

Any help would be greatly appreciated.

Please Login or Register  to view this content.

Where all the code that I added from your original macro that relates to the percentage calculation is:

Please Login or Register  to view this content.

Where the last line essentially says: Percentage = (B4-B3)/B3 but it is set up so that it varies based on the loop and the location I am calling. The problem either lies in the equation, or how I defined PrevRw or PrevCol.