In message of Wed, 16 May
2012 13:58:01 in microsoft.public.excel.programming, Gord Dibben
writes
You cannot name a new sheet same as an existing sheet unless you
delete existing first.

To simply add a new sheet with a name.............

Sub insert_sheet()
Dim newSht As Worksheet
Set newSht = Worksheets.Add
newSht.Name = "newSht"
End Sub

I dare say it is a question of comfort, but that seems much less simple
than Worksheets.Add.Name = "newSht"


If you want to add it again you must first delete it...............

Sub insert_sheet22()
Dim newSht As Worksheet
Dim wkSht As Worksheet
For Each wkSht In Worksheets
With wkSht
If .Name = "newSht" Then
Application.DisplayAlerts = False
Sheets("newSht").Delete
End If
End With
Next
Application.DisplayAlerts = True
Set newSht = Worksheets.Add
newSht.Name = "newSht"
End Sub


Are you intending this code to cater for both "newSht" existing and
absent before insert_sheet22 is called? I am more comfortable with

Sub insert_sheet22()
' Replace sheet "newSht"

Application.DisplayAlerts = False
Sheets("newSht").Delete
Application.DisplayAlerts = True

Worksheets.Add.Name = "newSht"
End Sub

or even

Sub insert_sheet22()
Const SheetName As String = "newSht" ' Replace that sheet

Application.DisplayAlerts = False
Sheets(SheetName).Delete
Application.DisplayAlerts = True

Worksheets.Add.Name = SheetName
End Sub

I am glad I checked that code. I originally wrote
Const String SheetName = "newSht" ' Replace that sheet
;)


Gord


On Wed, 16 May 2012 08:51:04 -0700 (PDT), Cindy Wang
wrote:

is there a way to control the sheet name you add? It could be called
sheet 1, sheet 2 or sheet3, etc. Is there a way to keep it same when
you add a new one? Or when you create a pivot, is there a way you
can always create from sheet1 when using a new sheet? I try to create
a macro, but could not control the sheet name and vloop would not be
able to find the sheet. Thanks,

--
Walter Briscoe