×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Function to rename pages

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?

Cyril Guichard
Mechanical Engineer

RE: Function to rename pages

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.

RE: Function to rename pages

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources