AW: Tabellenblätter erstellen mit Daten
04.01.2022 12:20:20
Rudi
teste mal:
Sub CreateSheets()
Dim strBlatt As String
Dim rngKunden As Range
Dim arrDaten, arrBlatt()
Dim lngKdNr As Long
Dim i As Long, j As Long, n As Long
Dim wksKunde As Worksheet, wksKdDaten As Worksheet
Set wksKdDaten = Sheets("Kundendaten")
arrDaten = wksKdDaten.Cells(1, 1).CurrentRegion
With Sheets("kunden")
For Each rngKunden In .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
n = 1
lngKdNr = Split(rngKunden)(0)
strBlatt = rngKunden
ReDim arrBlatt(1 To WorksheetFunction.CountIf(wksKdDaten.Columns(2), lngKdNr) + 1, 1 To UBound(arrDaten, 2))
For j = 1 To UBound(arrDaten, 2)
arrBlatt(1, j) = arrDaten(1, j)
Next
For i = 2 To UBound(arrDaten)
If arrDaten(i, 2) = lngKdNr Then
n = n + 1
For j = 1 To UBound(arrDaten, 2)
arrBlatt(n, j) = arrDaten(i, j)
Next j
End If
Next i
On Error Resume Next
Set wksKunde = Sheets(strBlatt)
On Error GoTo 0
If wksKunde Is Nothing Then
Set wksKunde = Worksheets.Add
wksKunde.Name = strBlatt
Else
wksKunde.Cells.Clear
End If
wksKunde.Cells(1, 1).Resize(UBound(arrBlatt), UBound(arrBlatt, 2)) = arrBlatt
wksKunde.Columns.AutoFit
Set wksKunde = Nothing
Next rngKunden
End With
End Sub
Gruß
Rudi