Note that you'll get a run-time error with this if a sheet already
exists with that name. For instance, if you have 5 sheets to begin
with, delete Sheet2, then add a sheet, Sheets.Count will return 5
and ActiveSheet.Name = "Sheet" & shct will give an error.

You may want to prevent that with something like:

Dim shct As Integer
shct = ActiveWorkbook.Sheets.Count - 1
On Error Resume Next
Do
shct = shct + 1
ActiveSheet.Name = "Sheet" & shct
Loop Until ActiveSheet.Name = "Sheet" & shct
On Error GoTo 0




In article ,
"Nikos Yannacopoulos" wrote:

I understand you are trying to copy Sheet1 in a new sheet
in the same workbook, and what troubles you is the name of
the new sheet? For instance, if you already have 4 sheets,
you would want the new one to be named Sheet5 rather than
Sheet1(2)? Try the following after you have copied the
sheet:

shct = ActiveWorkbook.Sheets.Count
ActiveSheet.Name = "Sheet" & shct