How to rename dynamic sheet in open workbook

[Ken]

General Question is How to rename dynamic sheet in open workbook ?

E.g. want to rename newly added sheet to another name.
Problem: sheetname varies on adding new sheet.

Below Steps Scenario
1) workbook is already open
2) workbook already has sheet1, sheet2, sheet4
(sheet3 deleted by the user for some reason)

3) Now the VBA code should be able to identify sheet number
or name after below code has adds new sheet in existing open workbook.

Sheets.Add after:=Sheets("MYMainSheet")

Comment: MS Excel gives number to newly added sheet as either sheet5 or sheet6 for already open workbook in which user may have created sheet5 and/or deleted it.

4) Question is how to determine by vba code what sheet number is given to newly added sheet is it going to be sheet5 or sheet6 or sheet7 or ..... any other number by giving by below command

Sheets.Add after:=Sheets("MYMainSheet")

Purpose: wanted to rename a sheet whenever user creates new sheet on an open workbook?

Currently code is written as below
Sheets.Add after:=Sheets("MYMainSheet")
Sheets("Sheet1").Name = "myWork"

Sheets.Add after:=Sheets("MYMainSheet")
Sheets("Sheet2").Name = "mySavedWrk"

In the above current code it is predefined and on error user has to delete sheet not required, save it, close it and reopen it manually to create sheet1.

Any suggestion, tried to explain as simpler as possible.

Thanks,

TechIT

 

[SkipVought]

Hi,

dim ws as worksheet

set ws = Sheets.Add(after:=Sheets("MYMainSheet"))

ws.name = "Whatever"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

If there is no need to refer to the new sheet by code, you can, basing on Skip's hint, combine two lines into one:
[tt]Sheets.Add(after:=Sheets("MYMainSheet")).Name = "myWork"[/tt]

combo