Renaming Problems

[Matt]

I am trying to get a macro to copy a worksheet and
rename it sheet sequentially. The way the macro needs
to be set up is so that, every time it copies the first
sheet and renames after the last sheet

The current name format is "Sheet1(2)"

Matt

 

[Nikos Yannacopoulos]

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

Nikos Y.

 

[Matt]

Thanks mate that worked and did just what I wanted.

Matt

 

[J.E. McGimpsey]

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