With the workbook open, try running the ResetAllUsedRanges macro below:
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
Sub ResetUsedRange(Optional wks As Worksheet)
Dim lngLastRow As Long, lngLastCol As Long, lngRealLastRow As Long, lngRealLastCol As Long
On Error Resume Next
If wks Is Nothing Then Set wks = ActiveSheet
lngLastRow = .Row
lngLastCol = .Column
lngRealLastRow = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
lngRealLastCol = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column
If lngRealLastRow < lngLastRow Then .Range(.Cells(lngRealLastRow + 1, 1), .Cells(lngLastRow, 1)).EntireRow.Delete
If lngRealLastCol < lngLastCol Then .Range(.Cells(1, lngRealLastCol + 1), .Cells(1, lngLastCol)).EntireColumn.Delete
What I've found is that with my 16 sheets selected I inserted a new column whereby excel gave me a "I haven't got the resources to do that mate!" error but did it anyway. On saving the workbook the file increased 10 fold - your code didn't have much effect on that size (a couple of mb) but thank you anyway.
Any ideas why the error should increase the workbook this much? I can only think that it is writing loads of error crap into the excel workbook program code!
I think I've found the problem - there is actually nothing in any of the sheets but I do have this in each sheets module:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 1 Then Target.Offset(, 29).Value = Now
If Target.Column = 2 Then Target.Offset(, 29).Value = Now
If Target.Column = 3 Then Target.Offset(, 29).Value = Now....etc
Which repeats for 21 cells so I think this is the resource issue - I've just deleted the code from all the sheet modules and it doesn't throw up an error when I add the columns over multiple sheets - so it may be cured, hopefully!
I know it's not a good idea to have all this code tracking changes but the system is for the Police who require evidence of data change and it's the only way I know how to do it!
Luckily I keep backup copies after major changes so can role back.