Check for Valid Sheet Name

So I have a macro that creates a new sheet and names it based off a cell value (date, MM-DD-YY), and copies a 'Template' sheet to it, values only. Every so often, two sheets need to be made with the same date. Could someone help me in the right direction in creating an addition to the below code that will add an A, B, or C at the end of the sheet name if the name is already taken?

Please Login or Register  to view this content.

Ah jindon has beaten me to the punch, but here's my suggested solution nonetheless:

Please Login or Register  to view this content.

HTH

Robert

Any help on the previously posted question will be appreciated!

try

Please Login or Register  to view this content.

Hi degmania,

Sorry it has taken me awhile to respond but it proved far tricker than I thought.

Have a look at the following which pops up an InputBox for the user to fill in if the proposed tab name already exists within the workbook. This way it will keep looping through until a unique name is found - i.e. not just limited to appending A, B or C to the existing proposed name.

Let me know how it goes and/or your thoughts.

Kind regards,

Robert

Please Login or Register  to view this content.

Jindon,

I have been trying out your code and received an error. Creating sheets with unique dates works fine. If I create a sheet with a date already in use, X is set to 8 and the macro will loop - continuously changing the letter, and freezing excel. This is beyond my knowledge, how can we make it so the trigger to exit the loop, x, is something other then 0 or >0? Novice example; X = unique sheet name Then Exit Do

OK
Can you change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Thank you for the effort! I am able to create sheets with unique names. When creating a sheet with a taken name, I receive the following error, on the line .Name = Newname;

'Run-time error '1004':Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic. '

For your reference I am using Excel 2007 and a file in compatibility mode.

I am learning a lot just working with your code, thanks.

If you are talking about my code, that line should be

Please Login or Register  to view this content.

As in my last code.

Jindon, the change corrected the error, many thanks! I will need to study this now so I can walk away from this a little more intelligent. I will comment the code with your name and this websites address.

Robert, as I am aiming for ease of use and minimal user action I am going to use Jindon's code. I appreciate all your effort on my behalf.

You have both shown great generosity!

Hi degmania,

That's strange - I originally tested it in Excel 2003 but have now just run it in Excel 2007 and it worked prefectly

Regards,

Robert

degmania,
CORRECTION!

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

Whole code

Please Login or Register  to view this content.