Change sheet's codename via code

mond007

New Member
Joined
Oct 9, 2008
Messages
34
Hi
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
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:p></o:p>
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:p></o:p>
<o:p></o:p>
e.g.<o:p></o:p>
<o:p></o:p>
sheets1 (Tracker)<o:p></o:p>
sheets6 (Summary Report)<o:p></o:p>
sheets80 (Bus STOCK ORDER)<o:p></o:p>
sheets81 (Bus PURCHASING)<o:p></o:p>
sheets82 (Bus ACCOUNTS)<o:p></o:p>
sheets83 (Bus DEBT CONTROL)<o:p></o:p>
etc.
<o:p></o:p>
I would like them to be like :
<o:p></o:p>
sheets1 (Tracker)<o:p></o:p>
sheets2 (Summary Report)<o:p></o:p>
sheets3 (Bus STOCK ORDER)<o:p></o:p>
sheets4 (Bus PURCHASING)<o:p></o:p>
sheets5 (Bus ACCOUNTS)<o:p></o:p>
sheets6 (Bus DEBT CONTROL)<o:p></o:p>
etc.
<o:p></o:p>
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:p></o:p>
Has anyone every done this ?
<o:p></o:p>
Kind Regards Kuldip.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

cornflakegirl

Well-known Member
Joined
Nov 4, 2004
Messages
2,023
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!
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Re: renumber sheet number (not sheet names)

In code (but include some error checking!!)

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,121
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,287
Messages
5,485,902
Members
407,523
Latest member
Talicius

This Week's Hot Topics

Top