Numbering Worksheets
 

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.

Numbering Worksheets
 

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


"jnf40" wrote:

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.

Numbering Worksheets
 

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 wrote:

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.

--

Dave Peterson

Numbering Worksheets
 

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" wrote:

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 wrote:

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.

--

Dave Peterson

Numbering Worksheets
 

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.

jnf40 wrote:

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" wrote:

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 wrote:

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.

--

Dave Peterson


--

Dave Peterson

Numbering Worksheets
 

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.

--
Regars,
Tom Ogilvy




"jnf40" wrote in message
...
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" wrote:

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 wrote:

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.

--

Dave Peterson

Numbering Worksheets
 

Ah...

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

(Or something like that.)

Tom Ogilvy wrote:

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.

--
Regars,
Tom Ogilvy

"jnf40" wrote in message
...
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" wrote:

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 wrote:

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.

--

Dave Peterson


--

Dave Peterson

Numbering Worksheets
 

Careful, he might withhold your fee!

--
Regards,
Tom Ogilvy


"Dave Peterson" wrote in message
...
Ah...

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

(Or something like that.)

Tom Ogilvy wrote:

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.

--
Regars,
Tom Ogilvy

"jnf40" wrote in message
...
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" wrote:

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 wrote:

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.

--

Dave Peterson


--

Dave Peterson

Numbering Worksheets
 

Typo alert.

He might withold my FREE.



Tom Ogilvy wrote:

Careful, he might withhold your fee!

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
Ah...

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

(Or something like that.)

Tom Ogilvy wrote:

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.

--
Regars,
Tom Ogilvy

"jnf40" wrote in message
...
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" wrote:

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 wrote:

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.

--

Dave Peterson


--

Dave Peterson

--

Dave Peterson

Numbering Worksheets
 

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" wrote:

Typo alert.

He might withold my FREE.



Tom Ogilvy wrote:

Careful, he might withhold your fee!

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
Ah...

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

(Or something like that.)

Tom Ogilvy wrote:

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.

--
Regars,
Tom Ogilvy

"jnf40" wrote in message
...
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" wrote:

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 wrote:

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.

--

Dave Peterson


--

Dave Peterson

--

Dave Peterson

Numbering Worksheets
 

Glad it worked for you.



jnf40 wrote:

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" wrote:

Typo alert.

He might withold my FREE.



Tom Ogilvy wrote:

Careful, he might withhold your fee!

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
Ah...

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

(Or something like that.)

Tom Ogilvy wrote:

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.

--
Regars,
Tom Ogilvy

"jnf40" wrote in message
...
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" wrote:

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 wrote:

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.

--

Dave Peterson


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson