Inserting and Naming Sheets

[jcranst]

This is probably very simple but I am not sure how to do it. Here is the code I recorded to insert a new sheet in a workbook.

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet34").Select
Sheets("Sheet34").Name = "Billed Summary"

My problem is the name "sheet34" is not always "sheet34" so the next line does not reference the right name. Can you give the sheet a name when you insert it?

[krjoshi]

Hi jcranst.

Can you please explain little bit more, what you are going to accomplish finally.

[Steve_Courts]

Hi,

First time for me answering a question!

Declare the sheet and a name first, then add that sheet. I'd also set up some code to check if the sheet name exists, see what I've done below (adds a sheet before Sheet2, but you might get your sheet name or the before sheet from somewhere else in your workbook or code).

Cheers
Steve

Sub AddSheet()

'''Declare a worksheet, string and a boolean'''
Dim sheet_NewSheet As Worksheet
Dim string_SheetName As String
Dim bool_SheetNameExists As Boolean

'''Set the test boolean to false'''
bool_SheetNameExists = False

'''Set the name of the sheet you want to add'''
string_SheetName = "Name of Sheet To Add"

'''Check that the sheet name doesn't already exist by looping through the worksheets collection'''
For Each ws In ActiveWorkbook.Worksheets
 
If ws.Name = string_SheetName Then
   bool_SheetNameExists = True
End If
 
Next ws

'''If the name doesn't exist, add it, if it does do something else (e.g. a message box, or alter the name)'''

If bool_SheetNameExists = False Then

    Set sheet_New = Sheets.Add(Worksheets("Sheet2"))
    sheet_New.Name = string_SheetName

Else

    '''Do something else'''
    If MsgBox("This sheet name already exists", vbOKOnly, "Error") = vbOK Then
    End If

End If

End Sub