PDA

View Full Version : Naming a sheet to the current year



Fr83
11-22-2017, 01:53 AM
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
11-22-2017, 08:07 AM
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
11-22-2017, 08:19 AM
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
11-24-2017, 01:50 AM
Thanks for the help here.