Need Help: Changing Sheet Index Property in VBA code

Hi,

I would like to have a code to change the sheet index property through VBA, I tried the below but it didn't work:

Sheets("Sales").CodeName = "Sheet2"
Sheets("Sales").Index = "Sheet2"

Please help, appreciate.

The Index is determined by the sheet's postition in the tab order. To change it, move the sheet.

Also .Index is a number, not a string.

By any chance, do you want to change the .Name of the sheet?

Oherwise you have to use something like

Sheet2.Move after:= Worksheets(Worksheets.Count)

to move it to the end

Code:

---

Sheets("Sales").CodeName = "Sheet2"
Sheets("Sales").Name = "Sheet2"

NewIndex = 3
Sheets("Sheet2").Move After:=Sheets(NewIndex)

---

Dear all,
I tried the above but still not working.

Code:

---

Sub M_snb()
  on error resume next
  Sheets("Sales").move sheets(Sales").index-1)
end sub

---

Code:

---

Sub M_snb()
  on error resume next
  Sheets("Sales").move ,sheets(Sales").index+1)
end sub

---

Sir,

The above code is giving error message.

1. What is the error message?

2. "I would like to have a code to change the sheet index property through VBA, I tried the below but it didn't work:"

You can't just change the .Index. You need to move the sheet


3. Try something like

Code:

---

Option Explicit

Sub MoveSheet()
    Dim i As Long
   
    i = Worksheets("Sheet2").Index
    'move sheet4 BEFORE sheet2
    If i - 1 = 0 Then
        MsgBox "cannot move before"
    Else
        Worksheets("Sheet4").Move before:=Worksheets(i)
    End If


    i = Worksheets("Sheet3").Index
    'move sheet1 AFTER sheet3
    If i + 1 = Worksheets.Count Then
        MsgBox "cannot move after"
    Else
        Worksheets("Sheet1").Move after:=Worksheets(i)
    End If
End Sub

---

The real goal of OP is unclear to me.
Changing of the sheet's index by moving sheet is described here.
The below is example of changing codenames at runtime

Code:

---

Sub Test()
 
    Dim Sh As Worksheet
   
  ' There should be only one (unique) "Sheet2" codename in the workbook.
  ' Try to rename codename of the present "Sheet2" to the unique name
    On Error Resume Next
    Set Sh = Sheets("Sheet2")
    If Sh.CodeName = "Sheet2" Then Sh.[_CodeName] = "Sheet" & Replace(Trim(Str(CDbl(Now))), ".", "")
    On Error GoTo 0
   
    ' Change codename of the Sheets("Sales")
    Set Sh = Sheets("Sales")
    Sh.[_CodeName] = "Sheet2"
 
End Sub

---