AW: Import eines Arbeitsblatts aus anderer Excel-D

Sub BlattImport()
Dim wbQuelle As Workbook, wksImport As Worksheet
Dim wbZiel As Workbook
Dim strName As String
'Verzeichnis der Quelldatei
Const strPfadQ As String = "C:\Lokale Daten\Test"    'ggf. Anpassen!!
'Name der Quelldatei
Const strDatei As String = "TestDatei.xls"           'ggf. Anpassen!!
'Name des zu importierenden Tabellenblatts
Const strBlatt As String = "Tabelle1"                'ggf. Anpassen!!
Set wbZiel = ActiveWorkbook
'Quelldatei schreibgeschützt öffnen
Set wbQuelle = Workbooks.Open(Filename:=strPfadQ & Application.PathSeparator _
& strDatei, ReadOnly:=True)
Application.ScreenUpdating = False
Set wksImport = wbQuelle.Worksheets(strBlatt)
With wksImport
.Copy After:=wbZiel.Sheets(wbZiel.Sheets.Count)
End With
wbQuelle.Close savechanges:=False
Application.ScreenUpdating = True
wbZiel.Activate
Set wksImport = ActiveSheet
strName = InputBox("Neuer Name des importierten Blatts", _
"Blatt-Import - Neuer Blattname", wksImport.Name)
If strName  "" Then
wksImport.Name = strName
End If
'Datenobjekte aufräumen
Set wksImport = Nothing
Set wbZiel = Nothing: Set wbQuelle = Nothing
End Sub