Numbering Multiple Sheets

[Guest]

I have multiple sheets in a workbook and I would like to have them
consecutivly numbered. Each sheet is one page, and the only way i can think
of numbering the pages is manually entering a page number in each sheet. I
know there has to be a way to have it done automatically. For instance, I
have 36 sheets with one page each. What I would like is for the first sheet
to start with a page number of 38, and from each sheet on, 39, 40, 41... Any
body know how to do this?

Thanks

 

[davesexcel]

maybe this could help
http://office.microsoft.com/en-gb/assistance/HP011180461033.aspx

 

[Guest]

I don't know of any way of setting Excel to consistently force starting new
sheets at a specific number.

But it can be done in code - this code will do it for you. Currently set up
so that the next sheet added will be numbered starting at 38. However, it
looks at the currently numbered sheets and will either start with the next
highest number, or 38 if there aren't any numbered that high yet.

Sub AddNamedSheets()
Dim MaximumNumberInUse As Integer
Dim LC As Integer ' Loop Counter
Dim AnySheet As Worksheet
Dim AnySheetName As String
Dim AnyNumber As String
'determine maximum number in use on sheets now
'
MaximumNumberInUse = 37 ' initialize to one below where we want to start
at
'look at all existing sheets to see where you are
For Each AnySheet In Worksheets
AnyNumber = ""
AnySheetName = AnySheet.Name
For LC = Len(AnySheetName) To 1 Step -1
If Mid$(AnySheetName, LC, 1) >= "0" And Mid$(AnySheetName, LC,
1) <= "9" Then
AnyNumber = Mid$(AnySheetName, LC, 1) & AnyNumber
Else ' encountered non-numeric character
Exit For ' end search
End If
Next
If AnyNumber = "" Then
AnyNumber = "0"
End If
If Val(AnyNumber) > MaximumNumberInUse Then
MaximumNumberInUse = Val(AnyNumber)
End If
Next
Sheets.Add
ActiveSheet.Name = "Sheet" & Trim$(Str$(MaximumNumberInUse + 1))
Range("A1").Select
End Sub

 

[Guest]

Look's like davesexcel interpreted your needs better than I did. I was
thinking you wanted new sheets that were added to the workbook to be numbered
starting as "Sheet38" etc, rather than just needing the printout/displayed
worksheets to be uniquely numbered.

davesexcel has the answer for you. I've got left over code for another day
to use somewhere ... Next time I'll try to understand the problem just a
little better.