How to stop sub if Tab found with same name

[Kevchenk0]

Hello all,

I have tried a few bits of code now to exit a sub which creates and names a new sheet if a sheet with the name already exists.

Ideally i'd like an info message to display alerting the user that their is an existing name and then for the operation to be aborted.

However I keep getting the sheet being named (2) instead which is not the desired result. Please see code below and attached example sheet.

If you click the button "Create focus list" then it will create a sheet from the template and name it based on whats in cell B6

Any ideas?

Please Login or Register  to view this content.

[Kevchenk0]

Example added

[rorya]

You could do something like this:

Please Login or Register  to view this content.

[Kevchenk0]

Thanks for this, It works great on the example sheet but when I put the code into my master sheet I have an issue because the next tab to performance hub isn't a generated sheet, its something else. So when I execute the macro it overwrites this sheet and gives an error. I can remedy it by moving the sheet to the left of the performance hub sheet but then the tab sequence doesn't flow right. How do I get it to miss the next tab after?

[rorya]

I don't follow. That code shouldn't care what order your sheets are in as it creates the new copy at the end of the workbook.

[Kevchenk0]

So if I change the :=Sheets(Sheets.Count) to ("The name of the last sheet")Then it works but what do I need to put if I want it to send it to the last sheet position?

[Kevchenk0]

Ah I see the issue. it doesn't like it if my template sheet is hidden.
.
Brings me to my next question.... Is it possible to hide sheets and still work with them?. Ideally I don't want the template sheet to be visible.

[rorya]

For most things, yes it is, but for copying sheets, you're better off unhiding, copying, then re-hiding.

[Kevchenk0]

Ok I've added code to make visible copy then make invisible which works fine,

I have an issue when adding a message box to inform that sheet already exists, it either works for both scenarios are none.

Could I get help on where I need to insert the msgbox code to make this work please.

Please Login or Register  to view this content.

[Kevchenk0]

Sorted it. Dim'd an integer and then used goto function to take me to the end if the duplicate was found.

Learning slowly but surely

Please Login or Register  to view this content.

[rorya]

You should avoid Goto statements if possible. All you need there is something like:

Please Login or Register  to view this content.