VBA Code to name a worksheet

[AndrewJ]

I have copied a worksheet from another Excel file and have been able to rename the new worksheet, which was "Sheet 1"

How do I use VBA code to rename the (Name) property in the properties of the worksheet?

 

[Andrew Poulsom]

After the copy the new sheet becomes the active sheet, so:

ActiveSheet.Name = "YourName"

 

[Mudface]

If you're referring to the worksheet's CodeName, you can only change this at design time through the properties window, not programatically.

 

[Richie(UK)]

If you're referring to the worksheet's CodeName, you can only change this at design time through the properties window, not programatically.

Sub AChangeOfName()
ThisWorkbook.VBProject.VBComponents("Sheet1").Name = "Mudface"
'change the codename for Sheet1 to Mudface
End Sub

:wink:

 

[starrchelly]

I want to have 12 worksheets all labeled with the months of the year. How do I get them to only have the first three letters of the month? For example: "Jan '14" "Feb '14" "Mar '14"..etc.

Thanks!

 

[UberNoob]

I want to have 12 worksheets all labeled with the months of the year. How do I get them to only have the first three letters of the month? For example: "Jan '14" "Feb '14" "Mar '14"..etc.

Thanks!

You have probably solved the issue by now. But, I came across your post while trying to solve pretty much the same problem. Here is what I came up with:

Public Sub InitializeWB()
Dim i As Integer
i = 1

Dim WrkSheet As Worksheet

For i = 1 To 12
If i > Worksheets.Count Then
Set WrkSheet = Sheets.Add(After:=Sheets(Worksheets.Count))
Else
Set WrkSheet = Sheets(Worksheets.Count)
End If

WrkSheet.Name = CStr(MonthName(i, True)) & " '14"
Next i
End Sub

Hope it helps.

 

[starrchelly]

You have probably solved the issue by now. But, I came across your post while trying to solve pretty much the same problem. Here is what I came up with:

Public Sub InitializeWB()
Dim i As Integer
i = 1

Dim WrkSheet As Worksheet

For i = 1 To 12
If i > Worksheets.Count Then
Set WrkSheet = Sheets.Add(After:=Sheets(Worksheets.Count))
Else
Set WrkSheet = Sheets(Worksheets.Count)
End If

WrkSheet.Name = CStr(MonthName(i, True)) & " '14"
Next i
End Sub

Hope it helps.

How would I capitalize the month names? I just want to have all twelve sheets say "JAN", "FEB", "MAR" ... etc. Thank you!

 

[warrengoff]

If you're referring to the worksheet's CodeName, you can only change this at design time through the properties window, not programatically.

I found this which does that

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">Sub change_code_name()
Dim wbk As Object, sheet As Object
ActiveWorkbook.VBProject.Name = "VBAProject"
Set wbk = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.CodeName)
wbk.Name = "wbk_code_name"
Set sheet = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.Sheets(1).CodeName)
sheet.Name = "sheet_code_name"
End Sub</code>vba - Change Sheet code name - Stack Overflow

 

[IJ_Alducente]

This helped me too. Thanks!!