+ Reply to Thread
Results 1 to 12 of 12

Debug Duplicate Sheet Names

  1. #1
    Registered User
    Join Date
    03-19-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    34

    Post Debug Duplicate Sheet Names

    I have 75 worksheets. Each sheet has a name established as a template. Each sheet name is in a column named "TABS" on a hidden sheet. I can change the name of each worksheet by entering a new name in cell C7 and it updates the new sheet name in the named range "TABS" on the hidden worksheet. I don't want any of the other sheets to have the same name. If I try to duplicate a sheet name this code goes into debug. How do I add to this code to look at the named range "TABS" and avoid duplicating a sheet name with a message that says "This sheet name already exist."



    Option Explicit
    Const origSheetName = "BLDG1"
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Address <> "$C$7" Then Exit Sub
    If Range("C7").Value = "" Then
    Target.Parent.Name = origSheetName
    Else
    Target.Parent.Name = Range("C7").Value
    End If
    End Sub

  2. #2
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,679

    Re: Debug Duplicate Sheet Names

    Try this

    Please Login or Register  to view this content.

  3. #3
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Debug Duplicate Sheet Names

    There was an error in my code. I'll post new code in a new reply.
    Sorry bakerman. Guess I was still working on mine when you posted yours.
    Last edited by gmr4evr1; 03-19-2016 at 01:13 PM.
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  4. #4
    Registered User
    Join Date
    03-19-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    34

    Re: Debug Duplicate Sheet Names

    It goes into debug and highlights the following. I don't know what "columns(1),0))" mean. Maybe that's the error. Not sure. The column M named "TABS" on the hidden sheet has the original sheet names in it. Top cell is name "TABS" and sheets are listed in cells M2 through M76. C7 on the worksheets is blank. When C7 is filled in it changes the worksheet from BLDG1 to what ever I enter into C7 as well as changing the cell in the "TABS" column on the hidden worksheet. If I delete C7 on the worksheet, the name reverts back to BLDG1 and the cell in the "TABS" column in the hidden worksheet reverts back to BLDG1.

    if not iserror(application.match(Range("C7").Value,sheets("TABS").columns(1),0)) then

  5. #5
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Debug Duplicate Sheet Names

    Not to step on anyones toes, but I couldn't come up with my own code for this so I modified bakerman's code.
    Change the "TABS" to whatever the sheet hidden sheet name is and see if that works.
    Last edited by gmr4evr1; 03-19-2016 at 02:02 PM.

  6. #6
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Debug Duplicate Sheet Names

    Also, change the
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    if you range is in column M

  7. #7
    Registered User
    Join Date
    03-19-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    34

    Re: Debug Duplicate Sheet Names

    It gives me the message box as soon as I exit the cell. The value I entered into C7 is unique to the other values in the named range.

  8. #8
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Debug Duplicate Sheet Names

    If it's different from all the other values in column M, you shouldn't be getting the message. Can you attach the workbook so we can take a look at it?
    Edit* You said "named range"...the code above looks at column M, not a range in column M. So if your Unique value appears anywhere in column M, you will get the message.
    Last edited by gmr4evr1; 03-19-2016 at 02:04 PM.

  9. #9
    Registered User
    Join Date
    03-19-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    34

    Re: Debug Duplicate Sheet Names

    Solved. Had the wrong column number. Thanks for your help!!!

  10. #10
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Debug Duplicate Sheet Names

    No problem. Thank you to bakerman for providing the initial code.

  11. #11
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,679

    Re: Debug Duplicate Sheet Names

    @ hineshines

    I apologize for misreading your question. I assumed TABS was name of hidden sheet instead of Named Range ON hidden sheet.

    @ gmr4evr1

    Thanks for the quick follow up.

  12. #12
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Debug Duplicate Sheet Names

    No problem.

+ 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. Replies: 1
    Last Post: 10-06-2014, 09:44 AM
  2. [SOLVED] Checking for duplicate sheet names
    By TheRobsterUK in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-23-2014, 06:34 AM
  3. Finding Duplicate Names in Excel Sheet
    By aquwap in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-02-2013, 12:56 PM
  4. Replies: 11
    Last Post: 10-21-2012, 01:40 AM
  5. [SOLVED] Move all highlighted duplicate names plus social security numbers to another sheet
    By Mel Shad in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 08-31-2012, 10:54 PM
  6. Duplicate a sheet onto a new one with customised column names
    By marsulein in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-18-2008, 11:53 PM
  7. Debug Problem, possibly involving sheet names?
    By svaiskau in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-12-2007, 05:50 PM

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