Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1548to1552
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

in zwei for-schleifen neues Worksheet erstellen

in zwei for-schleifen neues Worksheet erstellen
20.03.2017 16:15:03
Johannes
Hallo Leute,
Ich würde gerne in jedem dieser Schleifen ein neues Arbeitsblatt erstellen, für j=1 ist dies erfüllt, jedoch geht es für j=2 usw nicht mehr.. Kann mir hierbei jemand Helfen?
Vielen Dank

Sub Timesheetsbefüllen()
Dim i As Integer
Dim j As Integer
For j = 1 To 20
For i = 2 To 1000
If Worksheets("Einz").Cells(i, 10).Value = Worksheets("Personalnummermap").Cells(j, 1) Then
If Worksheets("Einz").Cells(i, 8).Value = "1" Then
If WorksheetExists(Tabelle25.Cells(j, 2).Value & "- Oktober") Then
Worksheets(Tabelle25.Cells(j, 2).Value & "- Oktober").Cells(Rows.Count, "A").End(  _
_
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 12).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Oktober").Cells(Rows.Count, "F").End(  _
_
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 5).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Oktober").Cells(Rows.Count, "G").End(  _
_
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 4).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Oktober").Cells(Rows.Count, "D").End(  _
_
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 14).Value
Else
Set nws = ThisWorkbook.Worksheets.Add
nws.Name = "neu"
Worksheets("Timesheet").Range("A1:J92").Copy _
Destination:=Worksheets("neu").Range("A1")
Worksheets("neu").Name = Tabelle25.Cells(j, 2).Value & "- Oktober"
Worksheets(Tabelle25.Cells(j, 2).Value & "- Oktober").Cells(2, 2) = Tabelle25.Cells(j,   _
_
2).Value & "- Oktober"
Worksheets(Tabelle25.Cells(j, 2).Value & "- Oktober").Cells(Rows.Count, "A").End(xlUp).  _
_
Offset(1).Value = Worksheets("Einz").Cells(i, 12).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Oktober").Cells(Rows.Count, "F").End(xlUp).  _
_
Offset(1).Value = Worksheets("Einz").Cells(i, 5).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Oktober").Cells(Rows.Count, "G").End(xlUp).  _
_
Offset(1).Value = Worksheets("Einz").Cells(i, 4).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Oktober").Cells(Rows.Count, "D").End(xlUp).  _
_
Offset(1).Value = Worksheets("Einz").Cells(i, 14).Value
End If
ElseIf Worksheets("Einz").Cells(i, 8).Value = "2" Then
If WorksheetExists(Tabelle25.Cells(j, 2).Value & "- November") Then
Worksheets(Tabelle25.Cells(j, 2).Value & "- November").Cells(Rows.Count, "A").End(  _
_
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 12).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- November").Cells(Rows.Count, "F").End(  _
_
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 5).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- November").Cells(Rows.Count, "G").End(  _
_
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 4).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- November").Cells(Rows.Count, "D").End(  _
_
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 14).Value
Else
Set nws = ThisWorkbook.Worksheets.Add
nws.Name = "neu"
Worksheets("Timesheet").Range("A1:J92").Copy _
Destination:=Worksheets("neu").Range("A1")
Worksheets("neu").Name = Tabelle25.Cells(j, 2).Value & "- November"
Worksheets(Tabelle25.Cells(j, 2).Value & "- November").Cells(2, 2) = Tabelle25.Cells(j,  _
_
2).Value & "- November"
Worksheets(Tabelle25.Cells(j, 2).Value & "- November").Cells(Rows.Count, "A").End(xlUp). _
_
Offset(1).Value = Worksheets("Einz").Cells(i, 12).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- November").Cells(Rows.Count, "F").End(xlUp). _
_
Offset(1).Value = Worksheets("Einz").Cells(i, 5).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- November").Cells(Rows.Count, "G").End(xlUp). _
_
Offset(1).Value = Worksheets("Einz").Cells(i, 4).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- November").Cells(Rows.Count, "D").End(xlUp). _
_
Offset(1).Value = Worksheets("Einz").Cells(i, 14).Value
End If
ElseIf Worksheets("Einz").Cells(i, 8).Value = "3" Then
If WorksheetExists(Tabelle25.Cells(j, 2).Value & "- Dezember") Then
Worksheets(Tabelle25.Cells(j, 2).Value & "- Dezember").Cells(Rows.Count, "A").End(  _
_
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 12).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Dezember").Cells(Rows.Count, "F").End(  _
_
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 5).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Dezember").Cells(Rows.Count, "G").End(  _
_
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 4).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Dezember").Cells(Rows.Count, "D").End(  _
_
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 14).Value
Else
Set nws = ThisWorkbook.Worksheets.Add
nws.Name = "neu"
Worksheets("Timesheet").Range("A1:J92").Copy _
Destination:=Worksheets("neu").Range("A1")
Worksheets("neu").Name = Tabelle25.Cells(j, 2).Value & "- Dezember"
Worksheets(Tabelle25.Cells(j, 2).Value & "- Dezember").Cells(2, 2) = Tabelle25.Cells(j,  _
_
2).Value & "- Dezember"
Worksheets(Tabelle25.Cells(j, 2).Value & "- Dezember").Cells(Rows.Count, "A").End(xlUp). _
_
Offset(1).Value = Worksheets("Einz").Cells(i, 12).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Dezember").Cells(Rows.Count, "F").End(xlUp). _
_
Offset(1).Value = Worksheets("Einz").Cells(i, 5).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Dezember").Cells(Rows.Count, "G").End(xlUp). _
_
Offset(1).Value = Worksheets("Einz").Cells(i, 4).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Dezember").Cells(Rows.Count, "D").End(xlUp). _
_
Offset(1).Value = Worksheets("Einz").Cells(i, 14).Value
End If
ElseIf Worksheets("Einz").Cells(i, 8).Value = "4" Then
If WorksheetExists(Tabelle25.Cells(j, 2).Value & "- Januar") Then
Worksheets(Tabelle25.Cells(j, 2).Value & "- Januar").Cells(Rows.Count, "A").End( _
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 12).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Januar").Cells(Rows.Count, "F").End( _
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 5).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Januar").Cells(Rows.Count, "G").End( _
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 4).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Januar").Cells(Rows.Count, "D").End( _
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 14).Value
Else
Set nws = ThisWorkbook.Worksheets.Add
nws.Name = "neu"
Worksheets("Timesheet").Range("A1:J92").Copy _
Destination:=Worksheets("neu").Range("A1")
Worksheets("neu").Name = Tabelle26.Cells(j, 2).Value & " - Januar"
Worksheets(Tabelle25.Cells(j, 2).Value & "- Januar").Cells(2, 2) = Tabelle25.Cells(j, 2) _
_
.Value & "- Januar"
Worksheets(Tabelle25.Cells(j, 2).Value & "- Januar").Cells(Rows.Count, "A").End(xlUp).  _
_
Offset(1).Value = Worksheets("Einz").Cells(i, 12).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Januar").Cells(Rows.Count, "F").End(xlUp).  _
_
Offset(1).Value = Worksheets("Einz").Cells(i, 5).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Januar").Cells(Rows.Count, "G").End(xlUp).  _
_
Offset(1).Value = Worksheets("Einz").Cells(i, 4).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Januar").Cells(Rows.Count, "D").End(xlUp).  _
_
Offset(1).Value = Worksheets("Einz").Cells(i, 14).Value
End If
ElseIf Worksheets("Einz").Cells(i, 8).Value = "5" Then
If WorksheetExists(Tabelle25.Cells(j, 2).Value & "- Februar") Then
Worksheets(Tabelle25.Cells(j, 2).Value & "- Februar").Cells(Rows.Count, "A").End(  _
_
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 12).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Februar").Cells(Rows.Count, "F").End(  _
_
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 5).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Februar").Cells(Rows.Count, "G").End(  _
_
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 4).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Februar").Cells(Rows.Count, "D").End(  _
_
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 14).Value
Else
Set nws = ThisWorkbook.Worksheets.Add
nws.Name = "neu"
Worksheets("Timesheet").Range("A1:J92").Copy _
Destination:=Worksheets("neu").Range("A1")
Worksheets("neu").Name = Tabelle25.Cells(j, 2).Value & "- Februar"
Worksheets(Tabelle25.Cells(j, 2).Value & "- Februar").Cells(2, 2) = Tabelle25.Cells(j,   _
_
2).Value & "- Februar"
Worksheets(Tabelle25.Cells(j, 2).Value & "- Februar").Cells(Rows.Count, "A").End(xlUp).  _
_
Offset(1).Value = Worksheets("Einz").Cells(i, 12).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Februar").Cells(Rows.Count, "F").End(xlUp).  _
_
Offset(1).Value = Worksheets("Einz").Cells(i, 5).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Februar").Cells(Rows.Count, "G").End(xlUp).  _
_
Offset(1).Value = Worksheets("Einz").Cells(i, 4).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Februar").Cells(Rows.Count, "D").End(xlUp).  _
_
Offset(1).Value = Worksheets("Einz").Cells(i, 14).Value
End If
ElseIf Worksheets("Einz").Cells(i, 8).Value = "6" Then
If WorksheetExists(Tabelle25.Cells(j, 2).Value & "- März") Then
Worksheets(Tabelle25.Cells(j, 2).Value & "- März").Cells(Rows.Count, "A").End(xlUp) _
_
.Offset(1).Value = Worksheets("Einz").Cells(i, 12).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- März").Cells(Rows.Count, "F").End(xlUp) _
_
.Offset(1).Value = Worksheets("Einz").Cells(i, 5).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- März").Cells(Rows.Count, "G").End(xlUp) _
_
.Offset(1).Value = Worksheets("Einz").Cells(i, 4).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- März").Cells(Rows.Count, "D").End(xlUp) _
_
.Offset(1).Value = Worksheets("Einz").Cells(i, 14).Value
Else
Set nws = ThisWorkbook.Worksheets.Add
nws.Name = "neu"
Worksheets("Timesheet").Range("A1:J92").Copy _
Destination:=Worksheets("neu").Range("A1")
Worksheets("neu").Name = Tabelle25.Cells(j, 2).Value & "- März"
Worksheets(Tabelle25.Cells(j, 2).Value & "- März").Cells(2, 2) = Tabelle25.Cells(j, 2).  _
_
Value & "- März"
Worksheets(Tabelle25.Cells(j, 2).Value & "- März").Cells(Rows.Count, "A").End(xlUp). _
Offset(1).Value = Worksheets("Einz").Cells(i, 12).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- März").Cells(Rows.Count, "F").End(xlUp). _
Offset(1).Value = Worksheets("Einz").Cells(i, 5).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- März").Cells(Rows.Count, "G").End(xlUp). _
Offset(1).Value = Worksheets("Einz").Cells(i, 4).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- März").Cells(Rows.Count, "D").End(xlUp). _
Offset(1).Value = Worksheets("Einz").Cells(i, 14).Value
End If
ElseIf Worksheets("Einz").Cells(i, 8).Value = "7" Then
If WorksheetExists(Tabelle25.Cells(j, 2).Value & "- April") Then
Worksheets(Tabelle25.Cells(j, 2).Value & "- April").Cells(Rows.Count, "A").End( _
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 12).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- April").Cells(Rows.Count, "F").End( _
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 5).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- April").Cells(Rows.Count, "G").End( _
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 4).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- April").Cells(Rows.Count, "D").End( _
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 14).Value
Else
Set nws = ThisWorkbook.Worksheets.Add
nws.Name = "neu"
Worksheets("Timesheet").Range("A1:J92").Copy _
Destination:=Worksheets("neu").Range("A1")
Worksheets("neu").Name = Tabelle25.Cells(j, 2).Value & "- April"
Worksheets(Tabelle25.Cells(j, 2).Value & "- April").Cells(2, 2) = Tabelle25.Cells(j, 2). _
_
Value & "- April"
Worksheets(Tabelle25.Cells(j, 2).Value & "- April").Cells(Rows.Count, "A").End(xlUp). _
Offset(1).Value = Worksheets("Einz").Cells(i, 12).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- April").Cells(Rows.Count, "F").End(xlUp). _
Offset(1).Value = Worksheets("Einz").Cells(i, 5).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- April").Cells(Rows.Count, "G").End(xlUp). _
Offset(1).Value = Worksheets("Einz").Cells(i, 4).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- April").Cells(Rows.Count, "D").End(xlUp). _
Offset(1).Value = Worksheets("Einz").Cells(i, 14).Value
End If
ElseIf Worksheets("Einz").Cells(i, 8).Value = "8" Then
If WorksheetExists(Tabelle25.Cells(j, 2).Value & "- Mai") Then
Worksheets(Tabelle25.Cells(j, 2).Value & "- Mai").Cells(Rows.Count, "A").End(xlUp). _
_
Offset(1).Value = Worksheets("Einz").Cells(i, 12).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Mai").Cells(Rows.Count, "F").End(xlUp). _
_
Offset(1).Value = Worksheets("Einz").Cells(i, 5).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Mai").Cells(Rows.Count, "G").End(xlUp). _
_
Offset(1).Value = Worksheets("Einz").Cells(i, 4).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Mai").Cells(Rows.Count, "D").End(xlUp). _
_
Offset(1).Value = Worksheets("Einz").Cells(i, 14).Value
Else
Set nws = ThisWorkbook.Worksheets.Add
nws.Name = "neu"
Worksheets("Timesheet").Range("A1:J92").Copy _
Destination:=Worksheets("neu").Range("A1")
Worksheets("neu").Name = Tabelle25.Cells(j, 2).Value & "- Mai"
Worksheets(Tabelle25.Cells(j, 2).Value & "- Mai").Cells(2, 2) = Tabelle25.Cells(j, 2).  _
_
Value & "- Mai"
Worksheets(Tabelle25.Cells(j, 2).Value & "- Mai").Cells(Rows.Count, "A").End(xlUp). _
Offset(1).Value = Worksheets("Einz").Cells(i, 12).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Mai").Cells(Rows.Count, "F").End(xlUp). _
Offset(1).Value = Worksheets("Einz").Cells(i, 5).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Mai").Cells(Rows.Count, "G").End(xlUp). _
Offset(1).Value = Worksheets("Einz").Cells(i, 4).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Mai").Cells(Rows.Count, "D").End(xlUp). _
Offset(1).Value = Worksheets("Einz").Cells(i, 14).Value
End If
ElseIf Worksheets("Einz").Cells(i, 8).Value = "9" Then
If WorksheetExists(Tabelle25.Cells(j, 2).Value & "- Juni") Then
Worksheets(Tabelle25.Cells(j, 2).Value & "- Juni").Cells(Rows.Count, "A").End(xlUp) _
_
.Offset(1).Value = Worksheets("Einz").Cells(i, 12).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Juni").Cells(Rows.Count, "F").End(xlUp) _
_
.Offset(1).Value = Worksheets("Einz").Cells(i, 5).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Juni").Cells(Rows.Count, "G").End(xlUp) _
_
.Offset(1).Value = Worksheets("Einz").Cells(i, 4).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Juni").Cells(Rows.Count, "D").End(xlUp) _
_
.Offset(1).Value = Worksheets("Einz").Cells(i, 14).Value
Else
Set nws = ThisWorkbook.Worksheets.Add
nws.Name = "neu"
Worksheets("Timesheet").Range("A1:J92").Copy _
Destination:=Worksheets("neu").Range("A1")
Worksheets("neu").Name = Tabelle25.Cells(j, 2).Value & "- Juni"
Worksheets(Tabelle25.Cells(j, 2).Value & "- Juni").Cells(2, 2) = Tabelle25.Cells(j, 2).  _
_
Value & "- Juni"
Worksheets(Tabelle25.Cells(j, 2).Value & "- Juni").Cells(Rows.Count, "A").End(xlUp). _
Offset(1).Value = Worksheets("Einz").Cells(i, 12).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Juni").Cells(Rows.Count, "F").End(xlUp). _
Offset(1).Value = Worksheets("Einz").Cells(i, 5).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Juni").Cells(Rows.Count, "G").End(xlUp). _
Offset(1).Value = Worksheets("Einz").Cells(i, 4).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Juni").Cells(Rows.Count, "D").End(xlUp). _
Offset(1).Value = Worksheets("Einz").Cells(i, 14).Value
End If
ElseIf Worksheets("Einz").Cells(i, 8).Value = "10" Then
If WorksheetExists(Tabelle25.Cells(j, 2).Value & "- Juli") Then
Worksheets(Tabelle25.Cells(j, 2).Value & "- Juli").Cells(Rows.Count, "A").End(xlUp) _
_
.Offset(1).Value = Worksheets("Einz").Cells(i, 12).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Juli").Cells(Rows.Count, "F").End(xlUp) _
_
.Offset(1).Value = Worksheets("Einz").Cells(i, 5).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Juli").Cells(Rows.Count, "G").End(xlUp) _
_
.Offset(1).Value = Worksheets("Einz").Cells(i, 4).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Juli").Cells(Rows.Count, "D").End(xlUp) _
_
.Offset(1).Value = Worksheets("Einz").Cells(i, 14).Value
Else
Set nws = ThisWorkbook.Worksheets.Add
nws.Name = "neu"
Worksheets("Timesheet").Range("A1:J92").Copy _
Destination:=Worksheets("neu").Range("A1")
Worksheets("neu").Name = Tabelle25.Cells(j, 2).Value & "- Juli"
Worksheets(Tabelle25.Cells(j, 2).Value & "- Juli").Cells(2, 2) = Tabelle25.Cells(j, 2).  _
_
Value & "- Juli"
Worksheets(Tabelle25.Cells(j, 2).Value & "- Juli").Cells(Rows.Count, "A").End(xlUp). _
Offset(1).Value = Worksheets("Einz").Cells(i, 12).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Juli").Cells(Rows.Count, "F").End(xlUp). _
Offset(1).Value = Worksheets("Einz").Cells(i, 5).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Juli").Cells(Rows.Count, "G").End(xlUp). _
Offset(1).Value = Worksheets("Einz").Cells(i, 4).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- Juli").Cells(Rows.Count, "D").End(xlUp). _
Offset(1).Value = Worksheets("Einz").Cells(i, 14).Value
End If
ElseIf Worksheets("Einz").Cells(i, 8).Value = "11" Then
If WorksheetExists(Tabelle25.Cells(j, 2).Value & "- August") Then
Worksheets(Tabelle25.Cells(j, 2).Value & "- August").Cells(Rows.Count, "A").End( _
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 12).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- August").Cells(Rows.Count, "F").End( _
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 5).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- August").Cells(Rows.Count, "G").End( _
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 4).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- August").Cells(Rows.Count, "D").End( _
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 14).Value
Else
Set nws = ThisWorkbook.Worksheets.Add
nws.Name = "neu"
Worksheets("Timesheet").Range("A1:J92").Copy _
Destination:=Worksheets("neu").Range("A1")
Worksheets("neu").Name = Tabelle25.Cells(j, 2).Value & "- August"
Worksheets(Tabelle25.Cells(j, 2).Value & "- August").Cells(2, 2) = Tabelle25.Cells(j, 2) _
_
.Value & "- August"
Worksheets(Tabelle25.Cells(j, 2).Value & "- August").Cells(Rows.Count, "A").End(xlUp).  _
_
Offset(1).Value = Worksheets("Einz").Cells(i, 12).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- August").Cells(Rows.Count, "F").End(xlUp).  _
_
Offset(1).Value = Worksheets("Einz").Cells(i, 5).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- August").Cells(Rows.Count, "G").End(xlUp).  _
_
Offset(1).Value = Worksheets("Einz").Cells(i, 4).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- August").Cells(Rows.Count, "D").End(xlUp).  _
_
Offset(1).Value = Worksheets("Einz").Cells(i, 14).Value
End If
ElseIf Worksheets("Einz").Cells(i, 8).Value = "12" Then
If WorksheetExists(Tabelle25.Cells(j, 2).Value & "- September") Then
Worksheets(Tabelle25.Cells(j, 2).Value & "- September").Cells(Rows.Count, "A").End( _
_
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 12).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- September").Cells(Rows.Count, "F").End( _
_
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 5).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- September").Cells(Rows.Count, "G").End( _
_
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 4).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- September").Cells(Rows.Count, "D").End( _
_
xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 14).Value
Else
Set nws = ThisWorkbook.Worksheets.Add
nws.Name = "neu"
Worksheets("Timesheet").Range("A1:J92").Copy _
Destination:=Worksheets("neu").Range("A1")
Worksheets("neu").Name = Tabelle25.Cells(j, 2).Value & "- September"
Worksheets(Tabelle25.Cells(j, 2).Value & "- September").Cells(2, 2) = Tabelle25.Cells(j, _
_
2).Value & "- September"
Worksheets(Tabelle25.Cells(j, 2).Value & "- September").Cells(Rows.Count, "A").End(xlUp) _
_
.Offset(1).Value = Worksheets("Einz").Cells(i, 12).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- September").Cells(Rows.Count, "F").End(xlUp) _
_
.Offset(1).Value = Worksheets("Einz").Cells(i, 5).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- September").Cells(Rows.Count, "G").End(xlUp) _
_
.Offset(1).Value = Worksheets("Einz").Cells(i, 4).Value
Worksheets(Tabelle25.Cells(j, 2).Value & "- September").Cells(Rows.Count, "D").End(xlUp) _
_
.Offset(1).Value = Worksheets("Einz").Cells(i, 14).Value
End If
End If
End If
Next i
Next j
End Sub

5
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Beispiel Mappe Bitte...owT
20.03.2017 16:26:36
Max2

WorksheetExists? Habe ich noch nie gehört...
20.03.2017 16:33:18
Max2

AW: WorksheetExists? Habe ich noch nie gehört...
20.03.2017 16:51:31
Werner
Hallo Max,
schätze mal eine benutzerdefinierte Funktion die Johannes uns nicht zeigt.
Gruß Werner
AW: WorksheetExists? Habe ich noch nie gehört...
20.03.2017 16:59:51
Johannes
Ich kann sie euch gerne zeigen, an der liegt es aber nicht.. Ich kann leider keine Mappe herzeigen.
Vermutlich liegt es daran, dass ich nws ja schon einmal benutze und es einem Worksheet zuordne.. Kann ich das vermeiden?
AW: in zwei for-schleifen neues Worksheet erstellen
21.03.2017 14:48:05
Rudi
Hallo,
tippst du gerne?
Sollte reichen:
Sub Timesheetsbefüllen()
Dim i As Integer
Dim j As Integer
Dim strWS As String, arrMonths
Dim NWS As Worksheet
arrMonths = Array(, 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8, 9)
For j = 1 To 20
For i = 2 To 1000
If Worksheets("Einz").Cells(i, 10).Value = Worksheets("Personalnummermap").Cells(j, 1)  _
Then
strWS = Tabelle25.Cells(j, 2).Value & "- " & MonthName(arrMonths(Worksheets("Einz"). _
Cells(i, 8).Value))
If WorksheetExists(strWS) Then
Set NWS = Worksheets(strWS)
Else
Set NWS = ThisWorkbook.Worksheets.Add
NWS.Name = strWS
Worksheets("Timesheet").Range("A1:J92").Copy _
Destination:=NWS.Range("A1")
NWS.Cells(2, 2) = strWS
End If
NWS.Cells(Rows.Count, "A").End(xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 12). _
Value
NWS.Cells(Rows.Count, "F").End(xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 5). _
Value
NWS.Cells(Rows.Count, "G").End(xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 4). _
Value
NWS.Cells(Rows.Count, "D").End(xlUp).Offset(1).Value = Worksheets("Einz").Cells(i, 14). _
Value
End If
Next i
Next j
End Sub
Gruß
Rudi
Anzeige

96 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige