I figured out why it wasn’t working. Somehow when I chose “Insert” between two sheets, the CodeName did not set correctly for it. So still not sure how reliable CodeName is, but it did seem to work.
Note: you can see the CodeName by choosing “View Code”
Mid("Sheet10", 6) will return 10 so there’s no need to take 2 digits like in your edit:
Mid("Sheet100", 6) will also return 100.
Additionally, I still recommend using Invoke VBA with a .vbs file.
The .vbs file will look like this:
Dim lastAddedSheet As Worksheet
Dim oneSheet As Worksheet
Set lastAddedSheet = Worksheets(1)
For Each oneSheet In Worksheets
If Val(Mid(oneSheet.CodeName, 6)) > Val(Mid(lastAddedSheet.CodeName, 6)) Then
Set lastAddedSheet = oneSheet
GetLastCreatedSheet = lastAddedSheet.Name
Then, in your Workflow, it will look like this:
That’s the way I would do it, but either way should work.