问VBA -运行此代码时获取运行时错误“1004”

EN

Option Explicit

Sub test2()

    Dim name1 As Range, size As Long
    Dim TblSht As Worksheet
    Dim advpSht As Worksheet

    ' set the worksheet object and trap errors in case it doesn't exist
    On Error Resume Next
    Set TblSht = ThisWorkbook.Worksheets("Tabelle1")
    On Error GoTo 0
    If TblSht Is Nothing Then ' in case someone renamed the "Expense" Sheet
        MsgBox "Tabelle1 sheet has been renamed", vbCritical
        Exit Sub
    End If

    ' set the worksheet object and trap errors in case it doesn't exist
    On Error Resume Next
    Set advpSht = ThisWorkbook.Worksheets("advprsrv")
    On Error GoTo 0
    If advpSht Is Nothing Then ' in case someone renamed the "Expense" Sheet
        MsgBox "advprsrv sheet has been renamed", vbCritical
        Exit Sub
    End If

    With advpSht
        ' safer way to get the last row from column "D" (since later on you use it in a Range in Column "D")
        size = .Cells(.Rows.Count, "D").End(xlUp).Row

        For Each name1 In .Range("D2:D" & size)
            If Trim(name1.Value2) <> "" Then
                ' ***** Not sure about the connection between the 2 sheets *****
                TblSht.Cells(name1.Row, 1).Value = LCase(name1.Value2 & " " & TblSht.Cells(name1.Row, name1.Column))
            End If
        Next name1            
    End With

End Sub