PDA

View Full Version : Need Help: Changing Sheet Index Property in VBA code



khaledocom
08-15-2016, 12:52 AM
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
08-15-2016, 03:30 AM
The Index is determined by the sheet's postition in the tab order. To change it, move the sheet.

Paul_Hossler
08-15-2016, 06:15 AM
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
08-15-2016, 07:03 AM
Sheets("Sales").CodeName = "Sheet2"
Sheets("Sales").Name = "Sheet2"

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

khaledocom
08-15-2016, 11:38 PM
Dear all,
I tried the above but still not working.

snb
08-16-2016, 01:27 AM
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
08-16-2016, 11:37 PM
Sir,

The above code is giving error message.

Paul_Hossler
08-17-2016, 06:33 AM
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
08-18-2016, 07:48 PM
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