Errors in Excel file preventing it from being saved...

[ajc623]

I have couple files using macros and have been using the same file format for almost 10 years, we have made slight changes over the years but for the most part they have remained the same and over the last two years no updates. Each year I create a new file for use that year and archive the previous years file and there is one sheet for each day of the year. Near the end of last year the two people who use these files the most started to have issues. I have rebuilt each file a couple times and that seems to fix it for a couple weeks and then suddenly we have the same issue pop up. I am confused why this happening and am hoping someone on here might have an idea. Happy to answer any questions.


This is the message we are getting. When clicking "continue" the same error just comes up again.

This is the Macro we run on the file

Sub DailyPrint()
'
' DailyPrint Macro
'
' Keyboard Shortcut: Ctrl+Shift+P
'
Sheets("Today").PrintOut Copies:=3
Sheets("Each Person").PrintOut Copies:=1
Sheets("Today").Copy Before:=Sheets(9)
Range("A1:C1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Today (2)").Range("A48:AI130,P1:AI47").Clear
If Weekday(Date) = vbFriday Then
Sheets("Today (2)").Name = Format(Date + 3, "mmddyy")
Else
Sheets("Today (2)").Name = Format(Date + 1, "mmddyy")
End If
NextWorkDay = Date + 1
'increment NextWorkDay if it is a weekend
If Weekday(NextWorkDay, vbMonday) > 5 Then
While Weekday(NextWorkDay, vbMonday) > 5
NextWorkDay = NextWorkDay + 1
Wend

End If
Sheets("Blank").Range("D2:O47").Copy Sheets("Today").Range("D2:O47")
Worksheets("Today").Activate

ActiveWorkbook.Save

End Sub

 

[rlv01]

'Repairing the file' messages are never good. At that point I usually begin to think in terms of salvaging the corrupted workbook by moving everything to a new workbook, because I have never had a 'repaired' file that did not give me further trouble down the line. My general method looks like this:

1. Make a backup of the original file.
2. Create a new folder somewhere.
3. Using the Visual Basic Editor (VBE), for each code module, user form, worksheet, class module - right click and export the file to the new folder. Don't forget the workbook code module.
4. Save the damaged/corrupt macro enabled .xlsm workbook to a new .xlsx file. That will strip out all the macros.
5. Close the new .xlsx file.
6. Re-open the new .xlsx file. (important that the close & re-open steps not be skipped).
7. Using the Visual Basic Editor (VBE), import each file (from the new folder) that you previously exported.
8. Save the .xlsx file as a new macro-enabled (.xlsm) workbook.
9. Test the new workbook to be sure everything is working.

If something goes wrong, you still have your unmodified saved backup file so this is a low-risk procedure.