listi.net

Archive a Sheet

If your spreadsheet gets too large you can

Delete sheets

Archive sheets

To delete a sheet

Right Click the sheet tab at the bottom

Select Delete

Press Delete when asked "Microsoft Excel will permanently delete this sheet. Do you want to continue?"

To delete a sheet in VBA I've recorded the process above, but still get a warning. To get rid of the warning:


application.DisplayAlerts = False

... Delete code

Application.DisplayAlerts = True

Archive a Sheet

You might not want to delete a sheet but save it in another Excel sheet. This is called Archiving

I've assigned CTRL Shift A to do this function

In the Auto_open Subroutine


Application.OnKey "^+A","ArchiveOnSheet"

And the routine does the following


Sub ArchiveOnSheet()
 If dispYN("Do you want to archive this sheet?") Then
    ActiveSheet.Move After:=Workbooks("ECM Reporting Macro Archive.xlsm") _
        .Sheets(1)
    Workbooks("ECM Reporting Macro Archive.xlsm").Save
    ThisWorkbook.Activate
 End If 
End Sub