Copy worksheet problem

[Comintern]

I've found that Excel treats me a lot better when I use explicit references instead of using the Active* objects and the Application collections. Try setting the Workbook explicitly:

Public Function AddSheetCopy(CopyName As String, NewName As String)
 
    Dim xlBook As Excel.Workbook, xlSource As Excel.Worksheet
 
    Set xlBook = xlApp.ActiveWorkbook                           'Get the current book.
    
    For Each xlSheet In xlBook.Worksheets                       'Make sure the NewName doesn't exist.
        If xlSheet.Name = NewName Then
            Debug.Print ("sheet exists")
            Exit Function
        End If
    Next xlSheet
    
    Set xlSource = xlBook.Worksheets(CopyName)                  'Get a ref to the source sheet.
    Call xlSource.Copy(, xlBook.Worksheets(xlBook.Worksheets.Count))    'Copy it.
    
    Set xlSheet = xlBook.Worksheets(xlBook.Worksheets.Count)    'Get a ref to the new copy.
    xlSheet.Name = NewName                                      'Name it.
    
    Set xlBook = Nothing
    Set xlSource = Nothing
 
End Function

Also, instead of just exiting if the CopyName is a duplicate, you can use this function to generate a garunteed unique name. It should always allow a rename to succeed if passed through it. You use something other than V.#, like Copy # of [Sheet], but the principle would be the same.

Public Function UniqueName(xlBook As Excel.Workbook, sName As String) As String
 
    Dim sTest As String, lCount As Long, lNum As Long, lTop As Long, bDuped As Boolean
    
    lTop = xlBook.Worksheets.Count                  'Get the number of sheets in the book.
    If lTop = 1 Then                                'One sheet, can't be a duplicate.
        UniqueName = sName                          'Return it.
        Exit Function
    End If
    
    sTest = sName                                   'Set the testing name to the passed arg.
    Do
        bDuped = False                              'Reset the duplicated flag.
        For lCount = 1 To lTop                      'Test the name against all sheets.
            If xlBook.Worksheets(lCount).Name = sTest Then
                bDuped = True                       'If it's found, set the flag.
            End If
        Next lCount
        If bDuped = True Then
            sTest = sName & " V." & CStr(lNum + 2)  'Try V.#
            lNum = lNum + 1                         'Increment the number counter.
        End If
    Loop Until bDuped = False                       'Loop until it's unique.
 
    UniqueName = sTest
 
End Function

It's called like this:

xlSheet.Name = UniqueName(xlSheet.Parent, NewName)