Solved auto archive in active sheet, and disable formulas in archiv

July 19, 2012 at 09:05:45
Specs: Windows XP
i am trying to create a weekly time and attendance sheet using excel. i have formulas that will change the dates every week in excel automatically on the active sheet. what i am trying to do is find a formula or macro that will take a copy of the active sheet and archive it weekly, creating another spreadsheet within the workbook, disabling any other formulas or macros in the archived sheet, and essentially capturing a screen shot of that week and keeping it. I also want to have certain cells within the active sheet cleared every week, and ready for a new week of data. any suggestions would be very helpful! new to vba, and not much trying to research this topic. thanks! if i need to provide anything else please let me know.

See More: auto archive in active sheet, and disable formulas in archiv

Report •

July 19, 2012 at 12:10:03
✔ Best Answer
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.

Sub ArchiveSheet()
'Save Active sheet name
   actName = ActiveSheet.Name
'Copy Active sheet to end of workbook
   Sheets(actName).Copy After:=Sheets(Sheets.Count)
'Get name for sheet from user
   archiveName = Application.InputBox _
      ("Please enter name for archived sheet")
'Rename last sheet
   Sheets(Sheets.Count).Name = archiveName
'Protect sheet
   Sheets(Sheets.Count).Protect Password:="Secret"
'Clear range in original sheet
   With Sheets(actName)
   End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

July 19, 2012 at 14:19:21
thanks! i will see how this works and let you know tomorrow. i really appreciate your time and assistance! :D

Report •

Related Solutions

Ask Question