Creating and Naming Worksheet in Excel VBA [closed]

Question

I have some very simple code that adds a new Worksheet, after the current worksheets, to an Excel document, and then changes its name to one entered in a text box on a userform. Works fine on a new workbook, however in a workbook that has a number of existing worksheets it creates the new worksheet, but does not rename it.

This only happens the first time you run this code, the next time it will run fine. The thing that makes it even stranger is that if you open the VBA editor to try and debug it, it then runs fine as well. This obviously makes finding the error pretty hard.

The code I'm using is here:

     Dim WS As Worksheet       Set WS = Sheets.Add(After:=Sheets(Worksheets.count))      WS.name = txtSheetName.value 

Pretty simple. I'm wondering if this problem is that it is trying to rename the sheet before it is properly created? Is there a better way to write this code?

Update: I've started debugging this using msgboxes, as opening the debugger makes the problem stop, and it seems that it just stops processing the code halfway through:

  Dim WS As Worksheet   MsgBox (WS Is Nothing)      Set WS = Sheets.Add(After:=Sheets(Worksheets.count))     '***** Nothing after this point gets processed *******     MsgBox (WS Is Nothing)     MsgBox WS.name      WS.name = txtSheetName.value     MsgBox WS.name 

Answer 1

http://www.mrexcel.com/td0097.html

Dim WS as Worksheet Set WS = Sheets.Add 

You don't have to know where it's located, or what it's name is, you just refer to it as WS.
If you still want to do this the "old fashioned" way, try this:

Sheets.Add.Name = "Test"