PDA

View Full Version : sh.Name



nsajeff
02-26-2009, 08:37 AM
Hi, I'm trying to add the following bit to my code:

sh.Name = "Page"

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
02-26-2009, 08:53 AM
Maybe try this one :
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
Charlize

nsajeff
02-26-2009, 09:03 AM
Maybe try this one :
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 Charlize

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

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

mdmackillop
02-26-2009, 09:16 AM
You're not far away. This assumes your original page is not Page 1


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

nsajeff
02-26-2009, 09:18 AM
Thanks very much mdmackillop. That's exactly what I needed.