Option Explicit
Sub DatenHolen2()
Const C_ZIELE As String = "ArrakisStdExport,MaterialienNass"
Dim arrZiele() As String
Dim arrE As Variant
Const C_TODO As String = "A1:Q112"
Dim ExportDatei
Dim WBZiel As Workbook, WSZiel As Worksheet
Dim WBQuelle As Workbook
ExportDatei = Application.GetOpenFilename("Micrsoft Excel-Dateien (*.xlsx),*.xlsx", , "Bitte jeweiligen Arrakis Std.-Export öffnen ...")
If ExportDatei = "Falsch" Then Exit Sub
Set WBQuelle = Workbooks.Open(ExportDatei)
Set WBZiel = ThisWorkbook
Application.ScreenUpdating = False
arrZiele = Split(C_ZIELE, ",")
For Each arrE In arrZiele
On Error GoTo fail
Set WSZiel = WBZiel.Worksheets(arrE)
On Error GoTo 0
WSZiel.Cells.Clear
Select Case arrE
Case "ArrakisStdExport"
WBQuelle.Worksheets("Auswertung nach AP").Range(C_TODO).Copy WSZiel.Cells(1)
Case "MaterialienNass"
WBQuelle.Worksheets("Bestellzeilen FM-FL").UsedRange.Copy WSZiel.Cells(1)
End Select
Next arrE
fail:
Select Case Err.Number
Case 0
Case 9
Set WSZiel = WBZiel.Worksheets.Add(After:=WBZiel.Sheets(WBZiel.Sheets.Count))
WSZiel.Name = arrE
Resume
Case Else
Call MsgBox("unbekannter Fehler in DatenHolen()", vbExclamation)
End Select
WBQuelle.Close False
Set WBZiel = Nothing
Set WBQuelle = Nothing: Set WSZiel = Nothing
Application.ScreenUpdating = True
End Sub