Hallo,
ich möchte bestimmte Tabellenblätter mit den Namen "SME*" und "SM*" in ein neues Workbook kopieren. Derzeit hab ich es etwas umständlich gelöst:
For Each objSh In ThisWorkbook.Sheets
If objSh.Name Like "SM" & "*" Then
ReDim Preserve strNamen(UBound(strNamen) + 1)
i = UBound(strNamen)
If i = 1 Then
objSh.Activate
SME = ActiveSheet.Name
End If
If i = 2 Then
objSh.Activate
SM0 = ActiveSheet.Name
End If
If i = 3 Then
objSh.Activate
SM1 = ActiveSheet.Name
End If
If i = 4 Then
objSh.Activate
SM2 = ActiveSheet.Name
End If
If i = 5 Then
objSh.Activate
SM3 = ActiveSheet.Name
End If
If i = 6 Then
objSh.Activate
SM4 = ActiveSheet.Name
End If
If i = 7 Then
objSh.Activate
SM5 = ActiveSheet.Name
End If
If i = 8 Then
objSh.Activate
SM6 = ActiveSheet.Name
End If
End If
Next
'
'werden die oder das Tabellenblatt mit dem Namen "SN*" in ein neues Workbook kopiert
'
If SM0 = "" Then
Sheets(SME).Select
Sheets(SME).Copy
GoTo nach_SMEs_kopieren
End If
If SM1 = "" Then
Sheets(Array(SME, SM0)).Select
Sheets(Array(SME, SM0)).Copy
GoTo nach_SMEs_kopieren
End If
If SM2 = "" Then
Sheets(Array(SME, SM0, SM1)).Select
Sheets(Array(SME, SM0, SM1)).Copy
GoTo nach_SMEs_kopieren
End If
If SM3 = "" Then
Sheets(Array(SME, SM0, SM1, SM2)).Select
Sheets(Array(SME, SM0, SM1, SM2)).Copy
GoTo nach_SMEs_kopieren
End If
If SM4 = "" Then
Sheets(Array(SME, SM0, SM1, SM2, SM3)).Select
Sheets(Array(SME, SM0, SM1, SM2, SM3)).Copy
GoTo nach_SMEs_kopieren
End If
If SM5 = "" Then
Sheets(Array(SME, SM0, SM1, SM2, SM3, SM4)).Select
Sheets(Array(SME, SM0, SM1, SM2, SM3, SM4)).Copy
GoTo nach_SMEs_kopieren
End If
If SM6 = "" Then
Sheets(Array(SME, SM0, SM1, SM2, SM3, SM4, SM5)).Select
Sheets(Array(SME, SM0, SM1, SM2, SM3, SM4, SM5)).Copy
GoTo nach_SMEs_kopieren
End If
If SM6 "" Then
Sheets(Array(SME, SM0, SM1, SM2, SM3, SM4, SM5, SM6)).Select
Sheets(Array(SME, SM0, SM1, SM2, SM3, SM4, SM5, SM6)).Copy
End If
Wie kann ich diesen Code etwas einfacher gestallten ?
Vielen Dank !
Thomas