Change sheet's codename via code

[mond007]

Hi
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have a workbook that has basic several worksheets but when I run a macro-report I am creating several dozen worksheet for each business unit. I delete these worksheets every time in order to create new statistics each time. The worksheet numbers ends up in the hundred.
<o></o>
I need to renumber the worksheet number (not the worksheet names). I know there is limit to the number of sheets of 255 but still it would be neat to renumber so that they are in ascending order. <o></o>
<o></o>
e.g.<o></o>
<o></o>
sheets1 (Tracker)<o></o>
sheets6 (Summary Report)<o></o>
sheets80 (Bus STOCK ORDER)<o></o>
sheets81 (Bus PURCHASING)<o></o>
sheets82 (Bus ACCOUNTS)<o></o>
sheets83 (Bus DEBT CONTROL)<o></o>
etc.
<o></o>
I would like them to be like :
<o></o>
sheets1 (Tracker)<o></o>
sheets2 (Summary Report)<o></o>
sheets3 (Bus STOCK ORDER)<o></o>
sheets4 (Bus PURCHASING)<o></o>
sheets5 (Bus ACCOUNTS)<o></o>
sheets6 (Bus DEBT CONTROL)<o></o>
etc.
<o></o>
Has anyone every done this. I have looked exhaustively and found many ways of reordering and renaming the worksheets(1).name etc but not the actually sheet number. I can also found sheets.index but hit a brick wall.
<o></o>
Has anyone every done this ?
<o></o>
Kind Regards Kuldip.

 

[cornflakegirl]

Re: renumber sheet number (not sheet names)

Welcome to the board.

You are referring to the sheet's codename. You can change it manually in the VB Editor using the Properties window. I don't know how to change it in code, but I'm sure someone else will if that's what you want!

 

[cornflakegirl]

Re: renumber sheet number (not sheet names)

Found some code at the bottom of this thread.

 

[VoG]

Re: renumber sheet number (not sheet names)

In code (but include some error checking!!)

ActiveWorkbook.VBProject.VBComponents(Sheets("Summary Report").CodeName) _
  .Properties("_CodeName") = "Sheet2"

 

[RoryA]

Re: renumber sheet number (not sheet names)

You would be better off, in my opinion, giving them a meaningful code name rather than sheet1, sheet2 etc. Also, be aware that changing the sheet's code name does not alter its index number.