Numbering Worksheets

[jnf40]

I have a workbook that adds worksheets, names them and sorts them...My sheet
names are fine as they are with the cell entry...On the worksheet itself I
have a cell with 'Sheet' typed in it then a blank cell named Sht_of_ , the
next cell has 'of' typed in it then a blank cell named Sht_of_1...Looks
something like this,
Sheet_____ of _____...I want the numbering to go into these cells named
Sht_of_ and Sht_of_1...So if I have 2 worksheets named DBL ARROW and
DBL ARROW (2)...then
worksheet DBL ARROW would have Sheet 1 of 2 and
worksheet DBL ARROW (2) would have Sheet 2 of 2
if another worksheet was created later and it's name was
DBL ARROW (3) then
sheet DBL ARROW cells would change to Sheet 1 of 3
sheet DBL ARROW (2) cells would change to Sheet 2 of 3...and
sheet DBL ARROW (3) cells would be Sheet 3 of 3.

 

[Halim]

Hi ,

Try to use this one in "ThisWorkbook" Module :
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Range("A1").Value = "Sheet " & ActiveSheet.Index & " of " & Sheets.Count
End Sub

Rgds,
Halim

 

[Dave Peterson]

This may get you close:

Option Explicit
Sub testme()

Dim MyNames() As String
Dim myCount() As Long
Dim wksCount As Long
Dim wks As Worksheet
Dim wCtr As Long
Dim wkbk As Workbook
Dim LastSpaceOpenParen As Long
Dim myAdjName As String
Dim res As Variant
Dim TestRng As Range
Dim CurNum As String
Dim ShtOfName As String

Set wkbk = ActiveWorkbook
ShtOfName = "sht_of_"

wksCount = wkbk.Worksheets.Count

wCtr = 0
ReDim MyNames(1 To wksCount)
ReDim myCount(1 To wksCount)
For Each wks In wkbk.Worksheets
If wks.Name Like "* (*)" Then
'just increment the count,
'the base name should be already in the list
LastSpaceOpenParen = InStrRev(wks.Name, " (")
myAdjName = Left(wks.Name, LastSpaceOpenParen - 1)
res = Application.Match(myAdjName, MyNames, 0)
If IsError(res) Then
wCtr = wCtr + 1
MyNames(wCtr) = myAdjName
Else
myCount(res) = myCount(res) + 1
End If
Else
wCtr = wCtr + 1
MyNames(wCtr) = wks.Name
myCount(wCtr) = 1
End If
Next wks

If wCtr = 0 Then
MsgBox "somthing went horribly wrong"
Exit Sub
End If

ReDim Preserve MyNames(1 To wCtr)
ReDim Preserve myCount(1 To wCtr)

'loop again
For Each wks In wkbk.Worksheets
Set TestRng = Nothing
On Error Resume Next
Set TestRng = wks.Range(ShtOfName)
On Error GoTo 0
If TestRng Is Nothing Then
'do nothing to this sheet
Else
If wks.Name Like "* (*)" Then
LastSpaceOpenParen = InStrRev(wks.Name, " (")
myAdjName = Left(wks.Name, LastSpaceOpenParen - 1)
'get rid of ()'s
CurNum = Mid(wks.Name, LastSpaceOpenParen + 2)
CurNum = Left(CurNum, Len(CurNum) - 1)
Else
myAdjName = wks.Name
CurNum = 1
End If

res = Application.Match(myAdjName, MyNames, 0)
If IsError(res) Then
MsgBox "this shouldn't happen!"
Exit Sub
Else
wks.Range(ShtOfName).Value _
= "Sheet " & CurNum & " of " & myCount(res)
End If
End If

Next wks
End Sub

Remember that if the sheet numbers have gaps (1), (2), (9), you'll end up with
"sheet 9 of 3".

 

[jnf40]

I tried your code and nothing happens...you said

'the base name should be already in the list

what list? do I need to reference the range that the user types in the sheet
name?
I have a range 'ShtTabName' the user would type in ARROW' then click a
button to create the worksheet and the sheet name would be 'ARROW' if they
clicked the button again it would make the second sheet with the name 'ARROW
(2)' ...if they typed in 'WORD' then my worksheet tabs, after the sort, would
be 'ARROW' 'ARROW (2)' 'WORD'....then if they typed 'ARROW' again then my
worksheet tabs, after the sort, would be
'ARROW' 'ARROW (2)' 'ARROW (3)' 'WORD'... I'm sure I'm missing
something simple but can't figure out what it is...

 

[Dave Peterson]

It sure is a lot of code for nothing to happen.

And the code builds an array with elements like:
Arrow
Dbl Arrow
Word

It doesn't care about the " (#)" stuff.

So if it finds " (#)", it knows that that "base name" (without the (#)) is
already in the array.

I'd try stepping through the code and see what happens.

 

[Tom Ogilvy]

The list is the list that the code builds.

if you have a sheet level name with the name of "sht_of_" on each sheet,
it worked fine for me with a sorted tab list. At least it does what Dave
intended - not exactly what I understood you to described, but close enough
to see a result.

 

[Dave Peterson]

Ah...

Sometimes you're stuck with the program you wrote--not the program you want
<vbg>.

(Or something like that.)

 

[Tom Ogilvy]

Careful, he might withhold your fee!

 

[Dave Peterson]

Typo alert.

He might withold my FREE.

Careful, he might withhold your fee!

 

[jnf40]

Okay it works...I recopied it into the module and it worked...thank you for
all your 'priceless' help, sorry couldn't help myself after Tom's and your
remarks...Both you and Tom have helped me immensely not only from my posts,
but the answers you both have given to others...Thanks again and I'm sure
I'll be seeking your guidance again.

 

[Dave Peterson]

Glad it worked for you.

Okay it works...I recopied it into the module and it worked...thank you for
all your 'priceless' help, sorry couldn't help myself after Tom's and your
remarks...Both you and Tom have helped me immensely not only from my posts,
but the answers you both have given to others...Thanks again and I'm sure
I'll be seeking your guidance again.