VBA Dateien öffnen und nur Werte kopieren

Sub MergeAllWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim NRow As Long
Dim FileName As String
Dim WorkBk As Workbook
Dim SourceRange As Range
Dim DestRange As Range
Set SummarySheet = Workbooks("Hauptdatei").Worksheets("Zielblatt")
' Modify this folder path
FolderPath = "C:\Users\"
' Call Dir the first time, pointing it to all Excel files in the folder path.
FileName = Dir(FolderPath & "*.xl*")
' Loop until Dir returns an empty string.
Do While FileName  ""
' Open a workbook in the folder
Set WorkBk = Workbooks.Open(FolderPath & FileName)
Dim loLetzte As Long, raFund As Range, i As Long
' alle Worksheets in den Dateien aus welchen die Zeilen kopiert werden sollen haben den namen " _
import"
Worksheets("Import").Activate
Application.ScreenUpdating = False
With Worksheets("Import") 'Quellblatt
loLetzte = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = 1 To loLetzte
Set raFund = Workbooks("Hauptdatei").Worksheets("Zielblatt").Columns("A:A").Find( _
What:=.Cells(i, 1), LookIn:=xlValues, lookat:=xlWhole)
If Not raFund Is Nothing Then
'Kopierbeeich an die Verhältnisse anpassen
.Range(.Cells(i, 2), .Cells(i, 900)).Copy raFund.Offset(, 1)
End If
Next i
End With
Set raFund = Nothing
Application.ScreenUpdating = True
' Close the source workbook without saving changes.
WorkBk.Close savechanges:=False
' Use Dir to get the next file name.
FileName = Dir()
Loop
' Call AutoFit on the destination sheet so that all
' data is readable.
SummarySheet.Columns.AutoFit
End Sub