Save copy on Open, keeping old backup copies

  • Hi y'all,
    I've searched over 4 different Excel help forums trying to find a way to do exactly what I want, but I am just not finding it. Could be I'm blind, but I'm just not finding it. So, I'm gonna have to post after all.

    I run a log of television & appliance returns. That's all it is, just a log of all the info. Because it has all the info that I need for the returns (serial, customer, when/if the return was filed, etc.) it is vitally important to my job. I've had to start from scratch once, and I don't ever want to have to do that again! The one thing that I've done a few times is change something & save it, and somehow screwed up everything else - then I can't remember what was what & where. I make changes to this log daily, sometimes several times a day.

    I want the log, titled RA Sheets & Log, saved in C:/Backup/Log Backup-mm/dd immediately upon open. That way, any changes I, or anyone else, make can be easily undone. Ideally, I'd be able to actually have 2 backup folders, one that's the every time it's opened backup & the other for an automated monthly backup. I would want the last 7 backups in the folder, anything older could be deleted. In the monthly backup I'd want one every month, like on the 1st or whatever.

    Any help y'all could give me is very much appreciated. I am a VERY inexperienced VBA & macro user. Since I'm almost 100% certain this solution will consist of either VBA or macros, or both, I better let you know to please tell me exactly where to put stuff. Telling me to insert a module, for example, is just gonna confuse me. lol

    Again, anything that y'all can do to help is appreciated. Please let me know if you have any questions. Thanks!


  • Re: Save copy on Open, keeping old backup copies

    try something like this

    right click the excel icon to the left of the menu "file"
    click view code and in the window that comes up copy this event code
    save the file

    Private Sub Workbook_Open()
    ThisWorkbook.SaveAs "C:/Backup/Log Backup" & worksheetfunction.Text(Date,"mmm-dd")

    note taht the date in the file name is today's date (date on which the file is opened)

    do some experimensts like this (I do not have such a folde to test)

    remember there should be a folder like
    c:\log backup\log backup
    if not open such a folder.

    there may a problem everyday it is saved in a new file. do'nt you want to delete older files.
    there may be other problems also may be revealed when you practically do it

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!