Dim Source As Excel.Workbook
    Dim Target As Excel.Worksheet
    Dim SourceL As Excel.Worksheet

    Set Source = Workbooks.Open(MacroPath, , True, , "111")
    Set SourceL = Source.Worksheets(SheetName)
 
    SourceL.Copy After:=Target

SourceL.Copy After:=Source.Worksheets(Source.Worksheets.Count)

SheetName="client"
MacroPath="C:\macro\macro_2014.xlsm"
ClientName="C:\doc\client2.xlsm"

Public Function CopySheet(SheetName As String) As Boolean

Dim Source As Excel.Workbook
Dim Target As Excel.Worksheet
Dim SourceL As Excel.Worksheet

'ïðîâåðèòü íàëè÷èå ëèñòà
If IsSheetPresent(SheetName) Then
'ëèñò åñòü
CopySheet = True
Else
CopySheet = False
'ëèñòà íåò - ñîçäàòü íîâûé
'ñîçäàòü ëèñò
Set Sh = Sheets.Add
Sh.Name = SheetName
'êóäà êîïèðîâàòü
Set Target = Workbooks(ClientName).Worksheets(SheetName)
'ñîõðàíÿåì ïîðÿäêîâûé íîìåð ëèñòà
IndexTarget = Target.Index
'îòêðûâàåì ôàéë
Set Source = Workbooks.Open(MacroPath, , True, , "111")
Set SourceL = Source.Worksheets(SheetName)
'Êîïèðîâàíèå ëèñòà Êëèåíò èç êíèãè ìàêðîñà â êíèãó Êëèåíòà
SourceL.Copy After:=Target ОШИБКА ЗДЕСЬ
'×òîáû íå ïîÿâëÿëèñü ïðåäóïðåæäàþùèå ñîîáùåíèÿ îá óäàëåíèè ëèñòà.
Application.DisplayAlerts = False
'óäàëèòü ëèñò Êëèåíò
Target.Delete
'×òîáû ïîÿâëÿëèñü ïðåäóïðåæäàþùèå ñîîáùåíèÿ òîãäà,
'êîãäà îíè äîëæíû ïîÿâëÿòüñÿ.
Application.DisplayAlerts = True
'ïåðåèìåíîâàòü ëèñò Êëèåíò2 â Êëèåíò
Worksheets(IndexTarget).Name = SheetName
'çàêðûâàåì êíèãó
Application.DisplayAlerts = False
Source.Close
Application.DisplayAlerts = True
End If

End Function 

 Set Target = Workbooks("C:\doc\client2.xlsm").Worksheets(SheetName)

 Set Target = Workbooks.Open("C:\doc\client2.xlsm").Worksheets(SheetName)

Public Function CopySheet(SheetName As String) As Boolean
    
    Dim Source As Excel.Workbook
    Dim Target As Excel.Worksheet
    Dim SourceL As Excel.Worksheet
    
    'проверить наличие листа
    If IsSheetPresent(SheetName) Then
        'лист есть - остановить копирование
        CopySheet = True
    Else
        CopySheet = False
        'листа нет - создать новый
        'создать лист
        Set Sh = Sheets.Add
        Sh.Name = SheetName
        'куда копировать
        Set Target = Workbooks(ClientName).Worksheets(SheetName)
        'сохраняем порядковый номер листа
        IndexTarget = Target.Index
        'открываем файл
        Set Source = Workbooks.Open(MacroPath, , True, , "111")
        Set SourceL = Source.Worksheets(SheetName)
        'Копирование листа Клиент из книги макроса в книгу Клиент
        SourceL.Copy After:=Target
        'Чтобы не появлялись предупреждающие сообщения об удалении листа.
        Application.DisplayAlerts = False
        'удалить лист Клиент
        Target.Delete
        'Чтобы появлялись предупреждающие сообщения тогда,
        'когда они должны появляться.
        Application.DisplayAlerts = True
        'переименовать лист Клиент2 в Клиент
        Worksheets(IndexTarget).Name = SheetName
        'закрываем книгу
        Application.DisplayAlerts = False
        Source.Close
        Application.DisplayAlerts = True
    End If
    
End Function