av8tordude
03-30-2011, 08:11 PM
I would like to copy, rename, and delete worksheets as follows...
My workbook will have 3 permanent sheets (Security, Master, f2106). I have a userform with 1-combo box (cboYear) and 1-Command button (cmdEnter).
Not counting the 3-permenant sheets (Security, Master, f2106)...
If no additional sheets exist (except Security, Master, & f2106), copy & rename the Master sheet with the current year (i.e. 2011).
If 1 sheet exist (i.e. 2011) + Security, Master, & f2106, copy & rename the Master sheet as 2012.
If 2 sheet exist (i.e. 2011, 2012) + Security, Master, & f2106, copy & rename the Master sheet as 2013.
If 3 sheet exist (i.e. 2011, 2012, 2013) + Security, Master, & f2106, copy & rename the Master sheet as 2014, but delete the oldest worksheet (i.e. 2011).
I have this code that copies and rename the Master sheet. Can someone assist with editing the code to accomplish the above. thanks
Note: Sheets ( Security, Master, & f2106) should NOT be deleted.
Dim MySheetName As String
If cboYear = "New Log" Then
ThisWorkbook.Unprotect Password:="MyPassword"
MySheetName = Year(Date)
Sheets("New Log").Copy before:=Sheets("f2106")
With ActiveSheet
.Unprotect Password:="MyPassword"
.Name = MySheetName
Range("B7") = "1/1/" & Year(Date)
.Protect Password:="MyPassword"
End With
ThisWorkbook.Protect Password:="MyPassword"
Me.Hide
Else
Sheets(cboYear.Value).Select
cmdEnter.Caption = "Enter"
Me.Hide
End If
My workbook will have 3 permanent sheets (Security, Master, f2106). I have a userform with 1-combo box (cboYear) and 1-Command button (cmdEnter).
Not counting the 3-permenant sheets (Security, Master, f2106)...
If no additional sheets exist (except Security, Master, & f2106), copy & rename the Master sheet with the current year (i.e. 2011).
If 1 sheet exist (i.e. 2011) + Security, Master, & f2106, copy & rename the Master sheet as 2012.
If 2 sheet exist (i.e. 2011, 2012) + Security, Master, & f2106, copy & rename the Master sheet as 2013.
If 3 sheet exist (i.e. 2011, 2012, 2013) + Security, Master, & f2106, copy & rename the Master sheet as 2014, but delete the oldest worksheet (i.e. 2011).
I have this code that copies and rename the Master sheet. Can someone assist with editing the code to accomplish the above. thanks
Note: Sheets ( Security, Master, & f2106) should NOT be deleted.
Dim MySheetName As String
If cboYear = "New Log" Then
ThisWorkbook.Unprotect Password:="MyPassword"
MySheetName = Year(Date)
Sheets("New Log").Copy before:=Sheets("f2106")
With ActiveSheet
.Unprotect Password:="MyPassword"
.Name = MySheetName
Range("B7") = "1/1/" & Year(Date)
.Protect Password:="MyPassword"
End With
ThisWorkbook.Protect Password:="MyPassword"
Me.Hide
Else
Sheets(cboYear.Value).Select
cmdEnter.Caption = "Enter"
Me.Hide
End If