Need Help: Changing Sheet Index Property in VBA code

[khaledocom]

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.

[Aflatoon]

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

[Paul_Hossler]

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

[SamT]

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

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

[khaledocom]

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

[snb]

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

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

[khaledocom]

Sir,

The above code is giving error message.

[Paul_Hossler]

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

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

[ZVI]

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

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