Add a new Sheets that (perhaps) already exists

[Maurizio]

How can I add a new Sheet named for example "PIPPO" (that perhaps already exists) avoiding any prompt with "error run-time 1004"?

sub addPippo
Sheets.Add
Sheets(ActiveSheet.Name).Name = "PIPPO"
end sub

 

[Richie(UK)]

Hi Maurizio,

As far as I am aware, you can't have two (or more) sheets with the same name. (Just imagine the confusion when trying to refer to the sheets!).

What I suggest that you do as part of your 'add a new sheet and name it' routine, is to check whether a particular sheet name exists and, if it does, and a number to the end then loop to check again. If that one also exists increment the number by one and check again and so on.

HTH

 

[Ekim]

Sub addPippo()
On Error GoTo ErrTrap

Sheets.Add
Sheets(ActiveSheet.Name).Name = "PIPPO"

ErrTrap:
Sheets("PIPPO").Activate
MsgBox "Duplicate name ", 16, "Warning " & ActiveSheet.Name
Err.Clear

End Sub

HTH

Mike

 

[Ivan F Moala]

On 2002-11-16 14:26, Ekim wrote:
Sub addPippo()
On Error GoTo ErrTrap

Sheets.Add
Sheets(ActiveSheet.Name).Name = "PIPPO"

ErrTrap:
Sheets("PIPPO").Activate
MsgBox "Duplicate name ", 16, "Warning " & ActiveSheet.Name
Err.Clear

End Sub

HTH

Mike

Mike, just a little glitch on the above.
You will need to put in an Exit sub and also a routine to delete the sheet when there
is a duplicate.

May I also suggest another option/way;

<pre/>
Sub Add_Sheet()
On Error Resume Next
With ActiveWorkbook
.Sheets.Add.Name() = "PIPPO"
If Err Then Application.DisplayAlerts = False: ActiveSheet.Delete
Application.DisplayAlerts = True
End With
On Error GoTo 0
End Sub
</pre>

 

[Maurizio]

Thanks, but I would want that the old sheet "Pippo" was replaced from the new.

 

[Ekim]

Ivan,

Good point.

Regards,

Mike