Function to rename pages
Function to rename pages
(OP)
Hi,
I'm working on writing a Sub to rename all pages of my woorkbook as "Page i" and including this page number in a cell as well.
The Sub is like follow :
Private Sub Workbook_Open()
For i = 1 To ActiveWorkbook.Sheets.Count
ActiveWorkbook.Worksheets(i).Name = "Page " & i
ActiveWorkbook.Worksheets(i).Cells(2, 2) = i
Next i
End Sub
It works all great untill I add or copy a page. When I do so, the function fails when I reopen my workbook.
Any idea?
I'm working on writing a Sub to rename all pages of my woorkbook as "Page i" and including this page number in a cell as well.
The Sub is like follow :
Private Sub Workbook_Open()
For i = 1 To ActiveWorkbook.Sheets.Count
ActiveWorkbook.Worksheets(i).Name = "Page " & i
ActiveWorkbook.Worksheets(i).Cells(2, 2) = i
Next i
End Sub
It works all great untill I add or copy a page. When I do so, the function fails when I reopen my workbook.
Any idea?
Cyril Guichard
Mechanical Engineer





RE: Function to rename pages
The error occurs since you try to rename a sheet with the same name as an already existing sheet. For example, if you insert a sheet between Page 2 and Page 3, named Sheet1, then the next time the workbook is opened, it tries to rename Sheet1 to Page 3 before it has renamed Page 3 to Page 4.
You can do two things:
1. Loop backwards through the sheets:
For i = ActiveWorkbook.Sheets.Count To 1 Step -1
....
Next i
2. Trap the error and rename the offending sheet (Page 3 in my example) to a temporary name:
Private Sub Workbook_Open()
Dim i As Integer
On Error GoTo ErrorHandler
For i = 1 To ActiveWorkbook.Sheets.Count
ActiveWorkbook.Worksheets(i).Name = "Page " & i
ActiveWorkbook.Worksheets(i).Cells(2, 2) = i
Next i
Exit Sub
ErrorHandler:
Select Case Err.Number
Case 1004 'error number for trying to rename a sheet with an existing name
ActiveWorkbook.Worksheets("Page " & i).Name = "TEMP" & i
ActiveWorkbook.Worksheets(i).Name = "Page " & i
End Select
Resume Next
End Sub
A combination of 1) and 2) would probably work most elegantly.
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Function to rename pages
Cyril Guichard
Mechanical Engineer