CODEWITHSUNDEEP
excelvba
luke
luke

Reputation: 482

Excel VBA store sheet index number for later use

I use this code to delete the active sheet and then copy a different sheet, How can I store the active sheet index number so I can use it to move the copied sheet to the stored index number?

Master_Work_Order.Visible = True


Dim oldSheetName As String
Dim Ws As Worksheet
Dim oldTabColor
Dim oldSheetIndexNumber

'Store
oldSheetName = ActiveSheet.NAme
oldTabColor = ActiveSheet.Tab.Color
' oldSheetIndexNumber = Store active sheet index number

ActiveSheet.Delete

Master_Work_Order.Copy After:=Sheets(ActiveSheet.Index)
Set Ws = ActiveSheet

'set new copied sheet name to previous ActiveSheet's name

Ws.NAme = oldSheetName
Ws.Tab.Color = oldTabColor
' Move active sheet to stored index number


Master_Work_Order.Visible = 2 'Makes sheet very hidden

Upvotes: 0

Views: 1449

Answers (1)

vknowles
vknowles

Reputation: 784

If I get your situation, this should work. I only tested it in a limited way. The .Copy method should put the new worksheet in the correct location.

Dim Ws As Worksheet
Dim oldTabColor
Dim oldSheetIndexNumber As Integer

'Store
oldSheetName = ActiveSheet.Name
oldTabColor = ActiveSheet.Tab.Color
oldSheetIndexNumber = ActiveSheet.Index

ActiveSheet.Delete

If ActiveWorkbook.Sheets.Count >= oldSheetIndexNumber Then
    ActiveWorkbook.Sheets("Sheet2").Copy before:=Sheets(oldSheetIndexNumber)
Else
    ActiveWorkbook.Sheets("Sheet2").Copy after:=Sheets(oldSheetIndexNumber - 1)
End If
Set Ws = ActiveSheet

'set new copied sheet name to previous ActiveSheet's name

Ws.Name = oldSheetName
Ws.Tab.Color = oldTabColor

Upvotes: 2

Related Questions