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

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 Target = Workbooks(ClientName).Worksheets(SheetCfg)
        'открываем файл
        Set Source = Workbooks.Open(MacroPath, , True, , "111")
        Set SourceL = Source.Worksheets(SheetName)
        'Копирование листа из книги макроса в книгу клиента
        SourceL.Copy Before:=Target
        'закрываем защищенную книгу
        Application.DisplayAlerts = False
        Source.Close
        Application.DisplayAlerts = True
    End If
    
End Function

Public Const SheetCfg As String = "cfg"

Sub Test()

    Dim Target As Excel.Worksheet
    Dim Source As Excel.Worksheet
    
    ClientName = ActiveWorkbook.Name
    'куда копировать
    Set Target = Workbooks(ClientName).Worksheets(SheetCfg)
    'откуда копировать
    Set Source = Workbooks(ClientName).Worksheets(SheetCfg)
    'Копирование листа
    Source.Copy Before:=Target
    
End Sub