Renaming Worksheets

[jimonfly]

I have a workbook that I inherited that uses a Select Case function to show/hide worksheets. Sheets are numbered in the order they were created (Sheet1, Sheet2, etc.) and ordered appropriately. I have created new sheets that were given names as Sheet90, etc. but I need them to move up in the order so I can use the existing code to show/hide. I can rename the sheet in Excel, but not the Sheet# in VBA, which is how the sheets are called. Is there any way to rename/re-order the sheet# in VBA?

The code is below, with each Case being a cell reference with a True/False statement. Specifically, Case M23 is what should be the end of the workbook. My sheets need to go before these, but are named Sheet75-Sheet 95. Not sure if I can have multiple Case commands for one specific cell. Thanks, Jim.

Private Sub Worksheet_Calculate()
For Each c In Range("M5:M34")
Select Case c.Address(0, 0)
Case "M5"
F = 5
T = 5
Case "M6"
F = 3
T = 4
Case "M7"
F = 7
T = 10
Case "M8"
F = 11
T = 12
Case "M9"
F = 14
T = 17
Case "M10"
F = 18
T = 18
Case "M11"
F = 19
T = 19
Case "M12"
F = 20
T = 21
Case "M13"
F = 22
T = 22
Case "M14"
F = 24
T = 27
Case "M15"
F = 34
T = 34
Case "M16"
F = 36
T = 36
Case "M17"
F = 37
T = 37
Case "M18"
F = 38
T = 38
Case "M19"
F = 39
T = 39
Case "M20"
F = 40
T = 40
Case "M21"
F = 41
T = 42
Case "M22"
F = 43
T = 43
Case "M23"
F = 45
T = 72
Case "M24"
F = 2
T = 2
Case "M25"
F = 6
T = 6
Case "M26"
F = 13
T = 13
Case "M27"
F = 23
T = 23
Case "M28"
F = 35
T = 35
Case "M29"
F = 29
T = 29
Case "M30"
F = 30
T = 30
Case "M31"
F = 28
T = 28
Case "M32"
F = 31
T = 31
Case "M33"
F = 32
T = 33
Case "M34"
F = 73
T = 73

End Select
For i = F To T
Sheets(i).Visible = c.Value = True
Next i

 

[VoG]

Is this any help? http://www.mrexcel.com/forum/showthread.php?t=484878

 

[jimonfly]

Thanks, I'll try it and see.

Jim