+ Reply to Thread
Results 1 to 5 of 5

Rename Excel Worksheets, Add Variable if worksheet name already exists

  1. #1
    Registered User
    Join Date
    10-10-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    9

    Rename Excel Worksheets, Add Variable if worksheet name already exists

    Hi everyone!

    I know there are a few threads regarding this issue but I can't seem to get any to work in my excel file. Currently, the macro renames worksheet based on last name "F16" and first 3 letters of first name "B16". When running this macro, I would like it to add a number to the worksheet if the worksheet name already exists. Example, if there are three "Mary Jane"s, one worksheet would be "Jane, Mar" and the other would be "Jane, Mar (2)" and "Jane, Mar (3)"

    Sub RenameSheet()

    Dim rs As Worksheet

    On Error GoTo ErrorHandler

    For Each rs In Sheets
    If rs.Name <> "Summary" Then
    rs.Name = rs.Range("F16") & ", " & Left$(rs.Range("B16").Value, 3)
    End If
    Next rs
    Exit Sub

    ErrorHandler:
    rs.Name = rs.Range("F16") & ", " & Left$(rs.Range("B16").Value, 3) & " (2)"
    Resume Next


    End Sub


    Any help and insight would be appreciated!!

  2. #2
    Forum Expert
    Join Date
    02-22-2013
    Location
    London / Stockholm
    MS-Off Ver
    Office 365
    Posts
    1,218

    Re: Rename Excel Worksheets, Add Variable if worksheet name already exists

    Hi jessello,

    try this:

    Please Login or Register  to view this content.

  3. #3
    Forum Expert
    Join Date
    02-22-2013
    Location
    London / Stockholm
    MS-Off Ver
    Office 365
    Posts
    1,218

    Re: Rename Excel Worksheets, Add Variable if worksheet name already exists

    A minor adjustment, if you want the second sheet to be named "Jane, Mar (2)" instead of "Jane, Mar (1)" like in my previous post, first define i=1

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    10-10-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Rename Excel Worksheets, Add Variable if worksheet name already exists

    Thanks Berlan!!
    I made minor adjustments to the code (because there's a "summary" worksheet and I don't want the macro to rename that) and it works beautifully

  5. #5
    Forum Expert
    Join Date
    02-22-2013
    Location
    London / Stockholm
    MS-Off Ver
    Office 365
    Posts
    1,218

    Re: Rename Excel Worksheets, Add Variable if worksheet name already exists

    Given that ws is set as the "summary" worksheet and that ws.name corresponds to this, the sheet should not be renamed

    Glad it works for you, and I appreciate the rep you gave me.

    All best

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] check if worksheet already exists when auto creating worksheets form a list
    By y0rk1e72 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-06-2012, 04:53 PM
  2. Using VBA to rename worksheets when a cell changes in first worksheet
    By Topdog in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-26-2012, 05:09 AM
  3. Deleting cell data in worksheet A if it exists in worksheets B, how?
    By Bern78 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-08-2011, 06:22 AM
  4. copying bold data from sheet1 to create new worksheets and rename the worksheet
    By ramdzan in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 03-03-2011, 11:33 AM
  5. [SOLVED] Worksheet Exists? Invisible Worksheets?
    By Ken Loomis in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-30-2005, 09:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1