Determine sheet number of new added sheet but InternalName alerady exists

I have a process that needs to take data from one worksheet to another.  The secnod worksheet is created in code and renamed.  However, the sheet name can be anything like "Sheet#".  However, since the worksheet that is added can be any sheet number, how do I determine what the sheet number is so I can refer to it in code?  That is, the new sheet's internal name can be say, Sheet16, but its tab name woudl be Sheet9 - which can be the internal name of an existing worksheet.  So, I need to determine the internal sheet name of the newly added worksheet.
Public Sub CreateWorksheet(strTabName)
'Each month creates a new worksheet
Dim strSheetName    As String
strSheetName = strTabName
    Sheets.Add After:=Sheets("CurrentVolumes")
    Sheets("Sheet8").Name = strTabName
    Sheets("CurrentVolumes").Select
    Columns("A:K").Select
    Selection.Copy
    Sheets("strTabName").Select
    Range("A1").Select
    ActiveSheet.Paste
End Sub

Open in new window

Sandra SmithRetiredAsked:
Who is Participating?
 
Patrick MatthewsCommented:
Use a variable to track it:



Public Sub CreateWorksheet(strTabName)
'Each month creates a new worksheet
Dim strSheetName    As String, Ws As Worksheet

strSheetName = strTabName
Set Ws = Worksheets.Add(After:=Worksheets("CurrentVolumes"))
    Ws.Name = strTabName
    Worksheets("CurrentVolumes").Range("A:K").Copy Ws.Range("A1")
End Sub

Open in new window

0
 
etech0Commented:
sheets.count
0
 
Sandra SmithRetiredAuthor Commented:
Sheets count won't work as I tried that, if the user adds and delete sheets during the process, the count will be off.  Matthews, this does the trick, thank you both.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.