Naming a sheet to the current year

[Fr83]

Hi all,

Just wondering if anyone can help. at year end im looking to store past data in a different sheet am currently thinking this,

Private Sub CommandButton1_Click()

Dim yearNum As StringyearNum = ActiveSheet.Range("A1")

MsgBox (yearNum)   
''''''in order to check what i get, as yearNum = Year(currDate) was just saying 1988

'''''here i want to try and check that the year doesnt already appear in one of the other sheets, just in case other users hit the button. dont know how to check this though,  hence asking for help here

Sheets("Sheet1").Copy after:=Sheets("Sheet1")
ActiveSheet.Name = yearNum

'''' ill then activate the first sheet and clear the contents. 
worksheets.sheet1.activate
Worksheets = "Sheet1".Range= ("A3:AB1000").ClearContents

If anyone could help with getting the code better and how to check for a duplicate sheet name would be greatful.

Thank.

[Paul_Hossler]

Maybe something like this

The Button Click event calls this macro in a standard module

Option Explicit

Sub RenameCurrentSheet()
    Dim yearNum As Long, n As Long
    Dim wsOriginal As Worksheet
    
    Set wsOriginal = ActiveSheet
    
    With wsOriginal
    
        yearNum = CLng(.Range("A1"))
    
        MsgBox "The year used to create a new worksheet is " & yearNum
    
        n = -1
        On Error Resume Next
        n = Worksheets(yearNum).Index
        On Error GoTo 0
        
        If n <> -1 Then
            MsgBox yearNum & " already exists"
            Exit Sub
        End If
        
        .Copy after:=wsOriginal
    End With
    
    ActiveSheet.Name = yearNum
     
    wsOriginal.Range("A3:AB1000").ClearContents
End Sub

[SamT]

Dim CurrentSheet As Worksheet
Set CurrentSheet = ActiveSheet

You need to set up a True If False test

On Error Resume Next
If Not Sheets(CurrentSheet.Range("A1")).Name = CurrentSheet.Range("A1")
'A Sheet's Name will never Not be the Sheet's Name, so the only way to get here 
'  is with an error such as the sheet does not exist yet
'
'Create your new sheet and name it here
'
Errox = o 'Fix this line
End If

[Fr83]

Thanks for the help here.