Macro not working

[HuskerBronco]

Can someone debug this code for me. I can't figure out why it isn't working.

Please Login or Register  to view this content.

Here is the goal of the code:

Check for a Worksheet with the name found in NamedRange "SheetName".
If Worksheet NOT found...then Create Worksheet named "SheetName".
If Worksheet FOUND...then MsgBox outlining User Options.

Option 1: DELETE old Worksheet and Create NEW Worksheet named "SheetName".
Option 2: GO TO old worksheet.


Option 2 works perfectly.
Option 1 results in Run-time error '13': Type mismatch
The error occurs on the following line:

Please Login or Register  to view this content.

I read online that sometimes...a copy/paste of VBA will cause this error.
So I deleted the line of code that was flagged...and typed it manually.
Obviously...that didn't work, or you wouldn't be reading this.
(It DID work, however, on another section of the code that was producing the same error).

Have I coded something wrong?? Or...is there some other problem??

Attached...is a sample Workbook...for examination.

[Andy Pope]

I just loaded the sheetname value in to a variable to avoid all the issues.

Please Login or Register  to view this content.

[HuskerBronco]

Of course, Andy...

I knew I was leaving out something simple.

I based that code on an example online.
The author of the original code also failed to assign the "SheetName" value to a variable.

Everything works as expected, now. Thanks...!!!

[broro183]

hi,

After typing up this post, I realised the thread is already marked as Solved, but since I've done the typing I thought I would post it anyway...

Here's a short function that uses the same principles for identifying if a sheet exists, but it is more portable because it's "wrapped" into a function.

Please Login or Register  to view this content.

This would allow you to test for the existence of the sheet with the same name before attempting to name (or even, before creating) the new sheet, for example:

Please Login or Register  to view this content.

Rob

[HuskerBronco]

Rob...

Thanks for responding to my thread.

Yes, my thread had already been marked "Solved" when you posted...
But...I'm glad you posted anyway. Your solution involves use of a UDF,
which is something I have not dabbled in, yet. It is something I
certainly want to explore...since I think it will allow me to construct
more versatile and efficient VBA code.

Your code is especially useful, since it can be used in ALL of my projects
that require worksheet detection.

Thanks, again....