How to retain the sheet index

[Emily]

Suppose I have made a copy of sheet 1 "Name XXX" in the same workbook and automatic named as Sheet11 "Name XXX(2)" and sheet 1 is deleted afterwards. How can I recover the index "Sheet 1" which viewed in VBA from "Sheet 11"?

 

[white6174]

Just a little unclear of your questine

steve w

 

[Emily]

Or simply, how can I use VBA to rename the worksheet name (or is it call index?)?

 

[parry]

Heres an example of copying sheet1 and renaming it test.

Sheets("Sheet1").Select
Sheets("Sheet1").Copy Before:=Sheets(1)
Sheets("Sheet1 (2)").Select
Sheets("Sheet1 (2)").Name = "test"

 

[Ivan F Moala]

On 2002-08-30 21:23, Emily wrote:
Suppose I have made a copy of sheet 1 "Name XXX" in the same workbook and automatic named as Sheet11 "Name XXX(2)" and sheet 1 is deleted afterwards. How can I recover the index "Sheet 1" which viewed in VBA from "Sheet 11"?

Hi Emily
To change the sheets code name try this;
Note you will need to reference the
Microsoft Visual basics extensibilty Library

<pre/>
Sub RenameSheetCodeName()

On Error GoTo ErrChg
With ActiveSheet
.Parent.VBProject.vbComponents(.CodeName).Properties("_CodeName") = "Sheet1"
End With

Exit Sub
ErrChg:
MsgBox Err.Number & ":=" & Err.Description, vbCritical
End Sub

</pre>

 

[Emily]

To: Ivan F Moala

There an error code 32813 appeared while I tried the code you given. I had already selected Microsoft Visual basics extensibilty Library 5.3. Can you fix it?

Thanks

I am using Window Me and Chinese Office XP.

 

[XL-Dennis]

Emily,

Tested Ivan´s code and it works as expected.

Plattforms:
Windows Me / XL 97 SR-2 (swedish)
Windows 2000 / XL 2000 SR-1 / XL 2002 XP (swedish)

How have You implemented it in the code?

Kind regards,
Dennis

 

[Emily]

I had tried to place the code in a module and in the active sheet.

Would you please show me the steps to execute the code correctly.

Thanks.

The same error code displayed when running on
Windows 2000 (Chinese)
Office 2000 (Chinese)
This message was edited by Emily on 2002-08-31 05:01

 

[stevebausch]

For sheets, there are (as you know) at least three ways of reference.

Indexing by .Name Sheets("SheetName") which indexes by the name on the sheet tab.

Indexing by number Sheets(1) which indexes by the sequence of sheet tabs.

Using the CodeName found in Project Explorer, which will be the same as .name, unless a user (Sheet tab) or a programmer (CodeName) changes something.

Moving the sheets around will disturb the numerical indexing of sheets, so unless you put replacement sheet in the same location as the previous sheet, the numerical index will change.

If you insert the new sheet before the 'old' sheet, and then delete, the same numerical index will work. And after the delete, the numerical index will refer to the new sheet.

 

[stevebausch]

From a MSN Search on "Changing Worksheet CodeName":

http://www.erlandsendata.no/english/vba/wb/codemodulenames.htm

http://www.add-ins.com/vbexmpls.htm

http://www.mrexcel.com/archive2/messages/18136.html


A warning about changing codenames:
http://www.cpearson.com/excel/codemods.htm

http://www.cpearson.com/excel/whatsnew.htm


And a MSN Search on "changing worksheet codename" returns the above hits, plus additional hits that are basically useless. Don't ask me why MSN Search is case-sensitive.