How do I renumber a worksheet

[Guest]

How do I renumber (reindex) a sheet using VBA.

Example:
sheet20(name) to sheet1(name)

 

[Guest]

In the project explorer in the VBE, select the entry for the sheet.

make the properties window visible. Change the (name) property.

 

[Guest]

Thanks, that works, but I have a need to be able to do this through VB code.
Do you know how?

 

[Guest]

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

look toward the bottom of the page. The argument to vbcomponents is the
codename.

 

[Guest]

I'm sorry, I still don't get it. I'm just looking for a small piece of code
that will change a sheet number from say sheet1 to sheet10.

 

[Gord Dibben]

Gary

In VBE you have Sheet1(sheetname) and you want to change the Sheet1 index name.

Run this macro......code from Chip's site.

Sub change()
ThisWorkbook.VBProject.VBComponents("Sheet1").Name = "NewCodeName"
End Sub

Now you have NewCodeName(sheetname)


Gord Dibben MS Excel MVP

 

[Tom Ogilvy]

Figure if you tell him the same thing 3 or 4 times he will pick it up?

 

[Guest]

Ogilvy,

You haven't told me the same thing 3 or 4 times you rude SOB. By the way,
you still haven't answered the questioned. Have a nice day.

 

[Tom Ogilvy]

Mr. May,

I believed I was talking to Gordon about what Gordon did (duplicating
information), not to you at all. (and it was meant to be a humorous chide
to him - the 3 or 4 was meant as a literary exaggeration)

Gordon and I both provided the same correct answer. My earlier response
giving you the source, while Gordon quoted part of it.

Sorry you chose to see my comment to Gordon as offensive to you. I've
read it several times and don't see it myself.

Post a new thread and perhaps you will get an answer that is more satisfying
to you. Use common, easily recognizable and specific terms like codename so
you don't mislead people and waste your time and their's with answers to
question you didn't intend to ask. You might also highlight the Link I gave
you or Gordon's quote from it and state that you don't see that as an
acceptable answer so people don't waste their time and yours in providing
that solution again. Also state that you are looking for a coding solution
so people don't tell you how to do it manually. (obviously tailor to meet
your true concerns - I don't want to put words in your mouth).

Again, sorry that you felt I was in some way being rude as there was never
any intention of that. I believe I made 4 honest attempts at providing you
an answer and in my final post, felt I had provided an answer.