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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.