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