Excel Tabelle mittels VBA Werte vergleichen und bei Übereinstimmung Namen zu weisen

Sub Zuordnen()
    dim sheet as Worksheet, rngSearchStart as Range, rngSearchEnd as Range, rngTargetStart as Range, rngTargetEnd as Range, cell as Range, foundCell as Variant
    Set sheet = Worksheets(1)
    Set rngSearchStart = sheet.Range("G1")  
    Set rngSearchEnd = rngSearchStart.End(xlDown)
    Set rngTargetStart = sheet.Range("A1")  
    Set rngTargetEnd = rngTargetStart.End(xlDown)
    
    For Each cell In sheet.Range(rngTargetStart, rngTargetEnd)
        Set foundCell = sheet.Range(rngSearchStart, rngSearchEnd).Find(cell.Value, LookIn:=xlValues)
        If Not foundCell Is Nothing Then
            cell.Value = foundCell.Offset(0, 1).Value
        End If
    Next
End Sub

sheet.Range("A1").Value = "Deine Überschrift " & date()

Sub CreatePivotTable()
    Dim rngData As Range, rngStart As Range, rngEnd As Range, sheet As Worksheet
    Set sheet = Worksheets(1)
    Set targetsheet = Worksheets(2)
    Set rngDestination = targetsheet.Range("A1")  
    Set rngStart = sheet.Range("B3")  
    Set rngEnd = rngStart.End(xlDown).Offset(0, 2)
    Set rngData = sheet.Range(rngStart, rngEnd)
    On Error Resume Next
    If Not targetsheet.PivotTables("Auswertung") Is Nothing Then  
        targetsheet.PivotTables("Auswertung").TableRange2.Clear  
    End If
    
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData).CreatePivotTable TableDestination:=rngDestination, TableName:="Auswertung"  
    
    With targetsheet.PivotTables("Auswertung").PivotFields("Herstellername")  
        .Orientation = xlRowField
        .Position = 1
    End With
    With targetsheet.PivotTables("Auswertung").PivotFields("TeileName")  
        .Orientation = xlColumnField
        .Position = 1
    End With
    targetsheet.PivotTables("Auswertung").AddDataField targetsheet.PivotTables("Auswertung").PivotFields("Anzahl"), "Summe von Anzahl", xlSum  
End Sub