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
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.
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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.