How to change sequential tab titles / names in a quick way?

[porkins]

Hello there! This is my 1st post and I apologies for the lack of proper introduction prior to jumping right in :p
My profession is that of a Product Engineer in a Disc Replication company and often need to work on some data spreadsheet using Excel.

I have a situation here that I already have a spreadsheet with 25 tabs each with a sequential number ie Page1, Page2, Page3 .... Page25.
Now due to some updates, I need to add a tab in between say Page3 & Page4.
So now I have a "Page3, "Sheet1", "Page4"... "Page25".
But my document is now with 26 pages physically. And I have to go through a painful (ok, not so painful) way of adding "1" to every tab from "Page4" onwards ie to make it "Page5" now (since "Sheet1" is now effectively a "Page4") all the way till changing "Page25" to "Page26".
And since it is sequential, is there a quick and fast way in doing this?

I appreciate any info and a thank you in advance

Cheers!
Kenny

[DonkeyOte]

Welcome to the Board.

You could perhaps use the NewSheet event @ Workbook level to do this...

Please Login or Register  to view this content.

The above would reside in the ThisWorkbook object in VB Editor.

Whenever a new sheet is added the code will be invoked and each sheet in the file will be renamed according to it's position in the file - it will work from last sheet to first so as to ensure you don't try and name two sheets with the same name!

[porkins]

Hello DonkeyOte (sorry for sounding disrespectful),

Thank you very very much for the codes. It worked like magic!
I shall now share this with all my other engineering colleagues. We are eternally grateful