Function to rename pages

[FrenchCAD]

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?

Cyril Guichard
Mechanical Engineer

 

[joerd]

Cyril,

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.

 

[FrenchCAD]

Thank you very much, I didn't thought about this problem of having twice the same sheet. It's really dump

Cyril Guichard
Mechanical Engineer