Copy worksheet, buttons and all and rename

[Dezertdog]

Hello All,
I have searched without success to find code I could adapt to what I want to do.
I want to copy my existing worksheet (named "2014") including the command buttons and the code that goes with them, to a new worksheet (named "2015") and so on every year.
And I would like this to happen automatically on December 28 each year.
I believe this is possible. But I am not versed enough in Excel to accomplish it. (so far)
Here is some code I have been playing with. I know it is far from what I really need.

I have it attached to a command button for quick testing...

Private Sub CommandButton1_Click()
Dim myWorksheet As Worksheet
    Dim myWorksheetName As String
    myWorksheetName = Format(Now, "yyyy")
    For Each myWorksheet In Worksheets
  
        If myWorksheet.Name = myWorksheetName Then
            MsgBox "Sheet already exists...Make necessary " & _
                "corrections and try again."
            Exit Sub
        End If
       
    Next myWorksheet
        Sheets.Add.Name = myWorksheetName
        Sheets(myWorksheetName).Move Before:=Sheets(Sheets.Count)
       
        
End Sub

Any help would be greatly appreciated.
Thanx in advance

D-Dog

 

[ozbod]

D-dog

Why don't you change that to "copy" the existing worksheet rather than "add" a worksheet

Assuming the first sheet is the one that you want to clone.

Private Sub CommandButton1_Click()
Dim myWorksheet As Worksheet
    Dim myWorksheetName As String
    myWorksheetName = Format(Now, "yyyy")
    For Each myWorksheet In Worksheets
  
        If myWorksheet.Name = myWorksheetName Then
            MsgBox "Sheet already exists...Make necessary " & _
                "corrections and try again."
            Exit Sub
        End If
       
    Next myWorksheet

[COLOR=#800080]    Sheets(1).Copy Before:=Sheets(1)
    Sheets(1).Name = myWorksheetName[/COLOR]   [COLOR=#00ff00]' Now a new Sheets(1)[/COLOR]                  End Sub

Note that if you want to automate it, you would work in the Workbook_Open event (in ThisWorkbook code area) to test your date criteria, but if your are going to increment the year why not use something like:

myWorksheetName = Val(Sheets(1).Name) + 1
Sheets(1).Copy Before:=Sheets(1)
Sheets(1).Name = myWorksheetName

Food for thought, but remember if you open the workbook several times during the 28th then it will keep running and adding 2016 etc, so you need another criteria to ensure that it can only be current year + 1

 

[Dezertdog]

Thank you ozbod, I will play with this a bit and see what i can put together.
I will post if it works.
D-Dog