Numbering sheets

  • Thread starter Thread starter Nick
  • Start date Start date
N

Nick

hi
i have managed to name all of the sheets the same
automaticly but when i save i need all of the sheets to
automaticly increase by one.
i.e. if all the sheets were called 20 when i save i need
them to become 21

thanks

NICK
 
Nick,

When you say that you have named all the sheets the same,
I presume you mean just the last two characters, so you
have:
RandomText_20
OtherRandomText_20
EvenMoreRandomText_20 etc

....and you want to up suffix to
RandomText_21
OtherRandomText_21
EvenMoreRandomText_21 etc

Is this about right? It easy to do with some code in the
BeforeSave event. Confirm what you need and I'll get back
to you...

Cheers,
Dave.
 
All of the sheets in excel are at the moment all numbered the same using
code.
when i go to save i need the sheet numbers to increase by one each time.

could someone please tell me how i would do this?
would i use VB for aplications???

thanks

NICK
 
I made some sheets that used numbers (1,2,3,4,...). No additional characters in
the names.

Then this worked ok for me:

Option Explicit
Sub testme2()

Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.Name = "XXXX_" & wks.Name
Next wks
For Each wks In ActiveWorkbook.Worksheets
wks.Name = Mid(wks.Name, 6, 255) + 1
Next wks

End Sub

If your names are consistently named, maybe you can use this idea. I prefixed
the names with XXXX_ to make them unique. If your names are already 31
characters long, then this idea won't work.
 
Back
Top