Prevent Errors for Duplicate Tab Names When Naming Tabs Using Cell Reference

Hello,

I have been looking through the forum for a while and I have not seen a clear response to this here or anywhere.

By way of background; I have a workbook that copies a template worksheet (using a macro button) and the user inputs their first and last name. It then formats the name using a first initial underscore last name scheme (i.e. J_Doe), It is this abbreviation that is then used to name the tab. The issue I run into, is there may be a John and Jane Doe, so I would like for the macro to identify when this error is going to occur and append a number to the back end of the users abbreviated name (i.e. J_Doe1). My current script achieves this, BUT it only allows for one duplication before an error occurs. I am looking for the macro to identify when the error is going to occur again and assign the next entry to J_Doe3 when naming the book. How can this be achieved, please see my script below.

Please Login or Register  to view this content.

Thank you!

Please Login or Register  to view this content.

This is great! Thank you so much. I ended up going with PCI's response. That one did exactly what I needed it to do. I am fairly new to VBA and I can get some basics done, but I rely a lot on these forums for help. Now that I see how you approached this, I am learning more about some different functions etc. Much appreciated!!!

This revised code does not work, the previous code you posted worked better. The only thing it does that is funny, is every time a cell changes it toggles the name of the sheet from (example: J_Doe to J_Doe1 and then back to J_Doe). It must be determining that the name already exists within itself and making the change. I should be able to work around that buy changing the macro to run when cell B4 is updated, rather than when the sheet is calculated.

If you can let me know what the new code does that the old code did not, I would like to hear it.

Thanks!