If you want to add new sheet one space before the last sheet. Name the current sheet as "X1Sheet" and have it become the active sheet, you would use some code like shown below:
Worksheets. Add(). Name = "X1Sheet"