How to retain the sheet index

Emily

Active Member
Joined
Aug 28, 2002
Messages
304
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"?
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Emily

Active Member
Joined
Aug 28, 2002
Messages
304
Or simply, how can I use VBA to rename the worksheet name (or is it call index?)?
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209

ADVERTISEMENT

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

Active Member
Joined
Aug 28, 2002
Messages
304
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

Well-known Member
Joined
Jul 27, 2002
Messages
1,920

ADVERTISEMENT

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

Active Member
Joined
Aug 28, 2002
Messages
304
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

Well-known Member
Joined
May 11, 2002
Messages
810
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

Well-known Member
Joined
May 11, 2002
Messages
810

Forum statistics

Threads
1,144,119
Messages
5,722,592
Members
422,447
Latest member
knopp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top