Filling cells

[jnf40]

Hi all,

I have a workbook that adds worksheets, names them and sorts them. I need to
show these worksheets as Sheet 1 of 1 and if necessary Sheet 1 of 2 and Sheet
2 of 2. When it adds the worksheets it names them based on a cell entry, so I
will have something like: DBL ARROW for worksheet1 name and it would be Sheet
1 of 1, if I need another sheet with the same name it would be DBL ARROW (2).
So I would now want DBL ARROW to be Sheet 1 of 2 and DBL ARROW (2) to be
Sheet 2 of 2, then if I added another sheet named 4" Yellow it would be Sheet
1 of 1 and so on. Is there a way to do this? I have ranges named Sht_of_ and
Sht_of_1 for the numbers.

 

[moon]

Public Sub AddWorkbook(nameInCell As String)
Dim wb As Workbook
Dim ws, cs As Worksheet
Dim v, x, y, z As Integer
Set wb = ThisWorkbook
x = wb.Worksheets.Count
z = 0
For y = 1 To x Step 1
If Left(wb.Sheets(y).Name, Len(nameInCell)) = nameInCell Then
z = z + 1
Set cs = wb.Sheets(y)
End If
Next y
cs.Activate
If z >= 1 Then
z = z + 1
Set ws = wb.Sheets.Add(after:=cs)
ws.Name = Left(cs.Name, Len(nameInCell)) & " - " & LTrim(Str(z)) & "
of " & LTrim(Str(z))
v = z - 1
For y = (x + 1) To 1 Step -1
Set cs = wb.Sheets(y)
If Left(cs.Name, Len(nameInCell)) = Left(ws.Name,
Len(nameInCell)) And cs.Name <> ws.Name Then
wb.Sheets(y).Name = nameInCell & " - " & LTrim(Str(v)) & "
of " & LTrim(Str(z))
v = v - 1
End If
Next
Else
wb.Sheets.Add after:=Worksheets(Worksheets.Count)
End If
Set cs = Nothing
Set ws = Nothing
Set wb = Nothing
End Sub


If there's a 'Sheet 1 of 1', the above thing wants to have that sheetname
and 'Sheet 2 of 2' will be added after 'Sheet 1 of 1'.
After that, 'Sheet 1 of 1' is also renamed to 'Sheet 1 of 2'.


You can pass the sheetname to the macro using a
Worksheet_BeforeDoubleClick-event.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
AddWorkbook (Target.Value)
End Sub


I hope this is what you want, but I liked to figure it out anyway if it's
not.

 

[jnf40]

Moon thank you for your response...the code you gave actually names the
sheets 1 of 2 and 2 of 2. I didn't explain myself too well. My sheet names
are fine as they are with the cell entry...where i need the sheet 1 of 1 is
in cells in the worksheet. On the worksheet itself I have a cell with 'Sheet'
in it then a blank cell named Sht_of_ , the next cell has 'of' in it then a
blank cell named Sht_of_1. I hope this explains it better.