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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Or simply, how can I use VBA to rename the worksheet name (or is it call index?)?
 
Upvote 0
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"
 
Upvote 0
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>
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Upvote 0

Forum statistics

Threads
1,218,742
Messages
6,144,220
Members
450,531
Latest member
avril18

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
Back
Top