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
|