I noticed this code is naming the wrong worksheet.. It assumes the newest
sheet is the one to be renamed and keeps renaming "sheet3". Any help
appreciated.

Public Function CopySheetAnalyst(xlSheet As Worksheet, Index) As Worksheet
'
' Macro2 Macro
' Macro recorded 7/11/2003 by SILVESC
'
Dim xName As String
Dim Basename As String
Dim x As Long

Call xlSheet.Copy(After:=Sheets(Sheets.Count))
Basename = Worksheets("CoverSheet").Range("B5").Offset(0, Index).Value
xName = Basename
On Error GoTo TryAgain
Sheets(Sheets.Count).Name = xName
On Error GoTo 0
Set CopySheetAnalyst = Sheets(Sheets.Count)
Exit Function
TryAgain:
x = x + 1
xName = Basename & "-" & x
Resume
End Function

It looks to me like it is renaming the sheet it is told to rename.

On Error GoTo TryAgain
Sheets(Sheets.Count).Name = xName
On Error GoTo 0
'Set CopySheetAnalyst = Sheets(Sheets.Count)
'Exit Function
'TryAgain:
x = x + 1
xName = Basename & "-" & x

It is told that the last sheet is xName then it is told to rename the last
sheet.


"TKM" wrote in message
...
I noticed this code is naming the wrong worksheet.. It assumes the newest
sheet is the one to be renamed and keeps renaming "sheet3". Any help
appreciated.

Public Function CopySheetAnalyst(xlSheet As Worksheet, Index) As Worksheet
'
' Macro2 Macro
' Macro recorded 7/11/2003 by SILVESC
'
Dim xName As String
Dim Basename As String
Dim x As Long

Call xlSheet.Copy(After:=Sheets(Sheets.Count))
Basename = Worksheets("CoverSheet").Range("B5").Offset(0, Index).Value
xName = Basename
On Error GoTo TryAgain
Sheets(Sheets.Count).Name = xName
On Error GoTo 0
Set CopySheetAnalyst = Sheets(Sheets.Count)
Exit Function
TryAgain:
x = x + 1
xName = Basename & "-" & x
Resume
End Function

The issue is in this line:
* * Sheets(Sheets.Count).Name = xName
This causes it to refer to the last sheet in the array of sheets. You
can refer to the first sheet like this:
Sheets(1).Name = xName
Or a named sheet like this:
Sheets("Sheet1").Name = xName

I like to just check in line instead of branching:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim dummy As Long
Set wks = CopySheetAnalyst(Worksheets("Sheet8"), dummy)
MsgBox wks.Name
End Sub
Public Function CopySheetAnalyst(xlSheet As Worksheet, Index As Long) _
As Worksheet

Dim xName As String
Dim Basename As String
Dim x As Long

xlSheet.Copy _
After:=xlSheet.Parent.Sheets(xlSheet.Parent.Sheets .Count)

Basename = "xxx"
'Basename = Worksheets("CoverSheet").Range("B5").Offset(0, Index).Value
xName = Basename
x = 0
Do
If x 0 Then
xName = Basename & "-" & x
End If
On Error Resume Next
xlSheet.Parent.Sheets(xlSheet.Parent.Sheets.Count) .Name = xName
If Err.Number < 0 Then
Err.Clear
x = x + 1
Else
'woohoo, it worked!
Exit Do
End If
Loop
Set CopySheetAnalyst = xlSheet.Parent.Sheets(xName)
End Function

=====
But if you want to use your code, start at Chip Pearson's site:
http://www.cpearson.com/Excel/ErrorHandling.htm

Ps. I changed your code so that the workbook is qualified (xlsheet.parent).





TKM wrote:

I noticed this code is naming the wrong worksheet.. It assumes the newest
sheet is the one to be renamed and keeps renaming "sheet3". Any help
appreciated.

Public Function CopySheetAnalyst(xlSheet As Worksheet, Index) As Worksheet
'
' Macro2 Macro
' Macro recorded 7/11/2003 by SILVESC
'
Dim xName As String
Dim Basename As String
Dim x As Long

Call xlSheet.Copy(After:=Sheets(Sheets.Count))
Basename = Worksheets("CoverSheet").Range("B5").Offset(0, Index).Value
xName = Basename
On Error GoTo TryAgain
Sheets(Sheets.Count).Name = xName
On Error GoTo 0
Set CopySheetAnalyst = Sheets(Sheets.Count)
Exit Function
TryAgain:
x = x + 1
xName = Basename & "-" & x
Resume
End Function

--

Dave Peterson