Need to modify macro to also create backup of original file

Good morning tfilipe


The snippet below needs to go after your ThisWorkbook.Save line, and you don't really need your ...Close line, as the code will shut Excel down.

Please Login or Register  to view this content.

As for only keeping a rolling 3-copy-backup, that is not as easy as it sounds. What else is in this directory?
It would make life miles easier if the only thing in this directory was your actual live file and the backups. Nothing else. Then all you need to do is set up your three backups manually, then get the code to delete the oldest file in there every time it is run.

HTH

DominicB

Hi tfilipe

Replace the line beginning fname with this :

Please Login or Register  to view this content.

The code at the bottom should hunt out and delete the oldest file in the directory that your workbook sits in.

The bit that you didn't follow : You will need to start with three backup files in this directory. The code will always delete the oldest file. If you don't start with three backup files, then the current one will always be deleted, and you will end up with no backup files. If you have three backup files to start with, your macro will create a fourth, my code will delete the oldest, and you are left with the most recent three. See?

This code will delete the file - it won't be put into a recycle bin. It will be gone. Do not test this on your live files until you are happy that it works as you expect.

Please Login or Register  to view this content.

At the moment, the "Kill" command at the bottom is commented out, so the code will tell you what the oldest file is. To make it live, uncomment the "Kill" line and remove the "MsgBox" line.

HTH

DominicB

Hi tfilipe

Can you post the workbook you're using?
You can delete any data in it, I don't need any of that - I just want to see the macros in situ and see how they're being called, and test them within the file.

HTH

DominicB

Hi tfilipe

OK. Replace the two macros you have above, with this one.
I've combined them both, tidied things up a bit and tested it and it seems to be working fine.

I have left the MsgBox line in there, but commented out. If you want to test the code first, comment out the "Kill" line, and activate the "MsgBox" line.

Please Login or Register  to view this content.

HTH

DominicB

Hi tfilipe

I've reworked the code a little to timestamp each filename with the time. I can't use "hh:mm:ss" as you cannot use the ":" character in filenames. I have gone for "hh-mm-ss") instead. It looks a bit strange but should do the trick.

I've also reversed the order the macro works in : the code now does the backup first and then saves the master file. This way the main file can never be the oldest file - don't know why i didn't pick up on that before.

As before, when you start using this, make sure that the folder contains your main file + the 3 backups. If your file works correctly, going forward there should never be more or less than 4 files in the folder.

Please Login or Register  to view this content.

Note that the "Kill" line is currently commented out.

HTH

DominicB