Save by month, delete cumulative backup flies

[itsunclebill]

I have a daily worksheet used to reconcile end of day business sales. Presently, when closing the file it asks if you want to backup the file and yes would be the "standard" reply.

The backup becomes quite large as it duplicates the entire workbook which adds a new sheet each day.

What I would like to do is have the backup process generate a copy of the workbook and delete any copy older than 10 days when the backup process runs.

I would also like to generate a new file named by month that stores all the current month backups and the prior 7 days. When a new backup is added to the file a prior month day would be deleted until only current month files remain in the workbook.

And, finally, helpful if but not necessary, a button that will show up on the last sheet in the monthly file that will allow you to delete all but the last backup for the month after 10 days.

The Idea here is that Files need to maintained long enough that errors can be tracked and fixed, and a week is long enough. After that, the files can be deleted as long as there is a copy of things for the accountant.

As to the rest of the worksheet, I built this for a friend and it is tailored to how he wanted things. However, ideas for improvement are certainly welcome. Thanks

[LeoTaxi]

Other method,
No more copy of sheets
by closing workbook or creating new sheet, day automaticly get stored in sheets "Historiek"
This way 1 year history takes 365 rows ( ok sometimes 366 )

On Sheet Backup, you can call every excisting day to show up


Kind regards
Leo

[itsunclebill]

Quite a lot of effort on this. It works well for saving spreadsheets with a few minor issues cleaned up. I got buried with the spreadsheet itself and didn't really have the time to improve things much beyond that.

Thanks
Bill

[itsunclebill]

Any ideas how the formulas that are on the original sheet can be transferred? The original spreadsheet automatically calculates a number of items and moves a number of totals from day ending to day beginning when a new sheet was created.

Thanks
Bill

[LeoTaxi]

The calculated items are stored in sheet Historiek
so by creating Backup on the Backup Sheet, Calculated fields get populated from there

Kind regards
Leo

[itsunclebill]

I see the sheet formulas are working. It appears that since a new sheet isn't created that totals moved from the prior sheet to the new sheet automatically in the old spreadsheet will need to accomplish this with a macro that runs when a new sheet is created.

It seems better to me that this be done when a new sheet gets created than to drag formulas down 365 or 366 lines on the backup sheet.

Example, on the spreadsheet, the cell labeled "ending sales total" on today's sheet need to be placed into "beginning sales total"
for the new sheet. Lotto sales need to transfer over, etc.

[LeoTaxi]

New exemple,

create new sheet and see if you mis something on new sheet

Kind regards
Leo

[itsunclebill]

I haven't had time to check everything as it's pretty detailed. But, what I have checked is great and all seems good. I'm sure if there is an issue with anything I'm sure it can be handled fairly easily.

Thanks for the interest in helping. I've needed to do something like this for a while and don't get into doing anything regularly. Takes me a while to retrain myself on stuff and it's frustrating to have to go back and figure out why you did something that way in the first place, even when commented. That is why this forum is such a great resource.