You will run into a problem if you try to rename a sheet to an mane that is
already in use. You need to test if the sheet name exists prior to attempting
the rename...

Public Function SheetExists(SName As String, _
Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function
--
HTH...

Jim Thomlinson


"ZipCurs" wrote:

My spreadsheet makes multiple copies of a base sheet and then modifies the
new sheets in different ways. The macro works great the first time I open
the file. If I try to run it again, it fails. The code is listed below:

NumberOfCategories = Sheets("EditCategories").Cells(1, 4) - 1 '(D1-1)
CategoryColumn = 5
TargetSheet = 13
LastColumn = CategoryColumn + NumberOfCategories

Do
Sheets(TargetSheet).Select
'THE CODE GENERALLY FAILS ON THE LINE BELOW WITH TargetSheet=14
Sheets(TargetSheet).Copy After:=Sheets(TargetSheet)
DoEvents

TargetSheet = TargetSheet + 1
Sheets(TargetSheet).Select

NameOfSheet = Worksheets(TargetSheet).Cells(3, CategoryColumn)
Sheets(TargetSheet).Name = NameOfSheet
CategoryColumn = CategoryColumn + 1
Loop Until CategoryColumn = LastColumn

Any help is appreciated, Thanks