Problem copying sheets
 

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

Problem copying sheets
 

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

Problem copying sheets
 

Jim, Thanks for the response. I do not believe that the problem is with
redundant names.

Once the code fails, if I exit and save, and then come back in, the code
runs fine. Any other thoughts are welcome.

"Jim Thomlinson" wrote:

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

Problem copying sheets
 

Further investigation reveals that the copy sheet function has simply
stopped. Closing and restarting Excel apparently restores the feature. Does
this help in suggesting a solution?

"Jim Thomlinson" wrote:

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