Changing (Name) property of a worksheet in excel 1

[apkohn]

In Excel, it appears that a worksheet can be identified one of three ways: by Index, by Name, or when looking in the properties sheet in the VBA window, by a property marked as (Name).

Now, I have a subroutine that copies the current worksheet and places it at the end of the workbook. Say the sheet has the following properties:
Index = 1, Name = "MySheet", (Name) = "Sheet1".

If I copy it, the new shett will have the following properties:
Index = 2, Name = "MySheet (2) ", (Name) = "Sheet11".

And copying that one, will give;
Index = 3, Name = "MySheet (3) ", (Name) = "Sheet111".

Now, using VB, is there anyway to firstly retrieve that property, and secondly set that property.

 

[uberpudge]

Hello Apkohn,

The (Name) property you are referring to is the CodeName property of a worksheet, which according to Microsoft Documentation is Read-Only at run-time.

If you go about defining an object variable as a VBE (Visual Basic Editor) instance it is technically possible to rename it at run-time.

I have tried this before and each time I have met with disasterous results (as in Excel-refuses-to-open-the-sheet-kinda-results...) If you want to try this route, best of luck to you.

Personally, I feel the best solution is simply to try something along the lines of:

Public Sub Test()
Dim shtCurrentSheet As Worksheet

For Each shtCurrentSheet In Worksheets
If Strings.InStr(1, shtCurrentSheet.CodeName, "Sheet", vbTextCompare) = 1 Then
'Do stuff to the sheet
End If
Next
End Sub


or


Public Sub Test()
Dim shtNewSheet As Worksheet

Set shtNewSheet = ActiveSheet.Copy

'Do stuff with shtNewSheet.
End Sub



Again, bets of luck,

Hope this helps,
Pete