sh.Name

[nsajeff]

Hi, I'm trying to add the following bit to my code:

[VBA]sh.Name = "Page"[/VBA]

I want to loop so that each time I create a new Worksheet it is given the name "Page #" until the end but I am stuck. Thank you.

[Charlize]

Maybe try this one :
[VBA]Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim lastsheet As Worksheet
Dim mysheetno As Long
mysheetno = 0
Set lastsheet = Sh
For Each Sh In ThisWorkbook.Worksheets
If Left(Sh.Name, 4) = "Page" Then
If Split(Sh.Name, "#")(1) > mysheetno Then
mysheetno = Val(Split(Sh.Name, "#")(1))
End If
End If
Next Sh
mysheetno = mysheetno + 1
lastsheet.Name = "Page # " & Str(mysheetno)
End Sub[/VBA]
Charlize

[nsajeff]

Maybe try this one :
[vba]Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim lastsheet As Worksheet
Dim mysheetno As Long
mysheetno = 0
Set lastsheet = Sh
For Each Sh In ThisWorkbook.Worksheets
If Left(Sh.Name, 4) = "Page" Then
If Split(Sh.Name, "#")(1) > mysheetno Then
mysheetno = Val(Split(Sh.Name, "#")(1))
End If
End If
Next Sh
mysheetno = mysheetno + 1
lastsheet.Name = "Page # " & Str(mysheetno)
End Sub[/vba] Charlize

Thanks Charlize, I have provided my code to show you what it is I'm trying to do:

[VBA]Public Sub CreateInvoices()
Const TEST_COLUMN As String = "A"
Dim i As Long
Dim z As Long
Dim LastRow As Long
Dim sh As Worksheet

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 3 To LastRow Step 41

Set sh = .Parent.Worksheets.Add(after:=.Parent.Worksheets(.Parent.Worksheets.Count))
sh.Name = "Page" (z)
.Rows(i).Resize(38).Copy sh.Range("A1")
Next i
End With

End Sub[/VBA]

[mdmackillop]

You're not far away. This assumes your original page is not Page 1

[VBA]
Option Explicit
Public Sub CreateInvoices()
Const TEST_COLUMN As String = "A"
Dim i As Long
Dim z As Long
Dim LastRow As Long
Dim sh As Worksheet

With ActiveSheet
LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 3 To LastRow Step 41
Set sh = .Parent.Worksheets.Add(after:=.Parent.Worksheets(.Parent.Worksheets.Count))
z = z + 1
sh.Name = "Page " & z
.Rows(i).Resize(38).Copy sh.Range("A1")
Next i
End With
End Sub

[/VBA]

[nsajeff]

Thanks very much mdmackillop. That's exactly what I needed.