|You will definitely need a macro to accomplish youR goals since a formula can't copy a sheet or "disable" formulas.|
This code will:
1 - Copy the ActiveSheet to the end of the workbook
2 - Ask for a name for the new sheet
3 - Rename the sheet
4 - Protect it with a password of "Secret" so that it is essentially "disabled"
5 - Clear B2:F100 of the original sheet that was copied
Obviously it will need to be modified to fit your exact needs.
'Save Active sheet name
actName = ActiveSheet.Name
'Copy Active sheet to end of workbook
'Get name for sheet from user
archiveName = Application.InputBox _
("Please enter name for archived sheet")
'Rename last sheet
Sheets(Sheets.Count).Name = archiveName
'Clear range in original sheet
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.