copy sheet data at the end of the month

[wasntme]

Hello all,

My goal is this: I have a tracker file with lots of formulas and dates, based on today date. I am looking for a way to make a copy of this sheet with values only on a specific date and time (lets say 23:59 on the last day of the month) in order to keep a log. Is this possible to be done at all and how?

Thanks in advance!

[JLGWhiz]

Is your computer power on 24/7?

[wasntme]

Yes, it is. Anyway can set another time to avoid any errors.

[JLGWhiz]

The easy way would be to use the task scheduler built into Windows.

In xl2007 and later

1.Open the Control Panel.
2.Open System and Security
3.Open the Task Scheduler icon.Under Administrative Actions
4.Select Scheduling method from the menu
5.Follow the Wizard instructions.

You would need a procedure in code module1 of your workbook to do the copying, You could substitute actual file and sheet name in the sample below.

Sample macro:

Please Login or Register  to view this content.

[wasntme]

Thank you JLGWhiz. This looks interesting, but does not fill my requirements as i would like to use only VBA. In order to get the desired result, maybe i can just leave the workbook open (to avoid using task scheduler, pc will be locked tho) and add some code to save a copy with values only, save itself and close at some time? Thanks in advance.

[JLGWhiz]

If you closed the workbook at night and opened it daily in the morning before a certain time (say 10 AM) then you could use a workbook event could like the example below.

Please Login or Register  to view this content.

The code would be installed in the ThisWorkbook code module. Then on the first day of each month when the workbook is opened, it will run the code and copy your data over.

[wasntme]

Thank you again JLGWhiz for your prompt reply and creative thinking. This is something i am trying to avoid as lot of the formulas depend on today(). So my other idea is to make this check on exit (some time range on last day of the month) and copy the file in a certain shared directory and overwrite existing one. This way i will have copy of the last saved version during the last day. Am i overcomplicating things or this makes sense?

[JLGWhiz]

I have been mentally running a scenario using the OnTime function and the EOMonth worksheet function to trigger the save action, but I cannot get it to gel into a logical result that would guarantee you file is saved on a monthly basis. If I do come up with something, I will post it to this thread. Meanwhile, you can hope that somebody smarter than me will pick up on the thread and offer a workable solution. There are problems with a self calling OnTime macro (I won't elaborate here) that can make them unreliable in a case like yours.
Regards, JLG