Sub ConvertPunchRow()
Dim NewRow As Long
Dim i As Long
NewRow = 1
For i = 2 To 1000
If ThisWorkbook.Sheets(1).Cells(i, 5).Value = vbNullString Then
' Emp shift info
ThisWorkbook.Sheets(1).Cells(i, 1).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow, 1)
ThisWorkbook.Sheets(1).Cells(i, 2).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow, 2)
ThisWorkbook.Sheets(1).Cells(i, 3).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow, 3)
' no breaks so just Start and End
ThisWorkbook.Sheets(1).Cells(i, 4).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow, 4)
ThisWorkbook.Sheets(1).Cells(i, 13).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow, 5)
NewRow = NewRow + 1
GoTo ReadNextLine
' = = = 1 Break = = =
End If
If ThisWorkbook.Sheets(1).Cells(i, 7).Value = vbNullString Then
'1st new row
ThisWorkbook.Sheets(1).Cells(i, 1).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow, 1)
ThisWorkbook.Sheets(1).Cells(i, 2).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow, 2)
ThisWorkbook.Sheets(1).Cells(i, 3).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow, 3)
' start to 1st break
ThisWorkbook.Sheets(1).Cells(i, 4).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow, 4)
ThisWorkbook.Sheets(1).Cells(i, 5).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow, 5)
'2nd new row
ThisWorkbook.Sheets(1).Cells(i, 1).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 1, 1)
ThisWorkbook.Sheets(1).Cells(i, 2).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 1, 2)
ThisWorkbook.Sheets(1).Cells(i, 3).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 1, 3)
' last shift
ThisWorkbook.Sheets(1).Cells(i, 6).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 1, 4)
ThisWorkbook.Sheets(1).Cells(i, 13).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 1, 5)
NewRow = NewRow + 2
GoTo ReadNextLine
' = = = 2 Breaks = = =
End If
If ThisWorkbook.Sheets(1).Cells(i, 9).Value = vbNullString Then
'==1st new row
ThisWorkbook.Sheets(1).Cells(i, 1).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow, 1)
ThisWorkbook.Sheets(1).Cells(i, 2).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow, 2)
ThisWorkbook.Sheets(1).Cells(i, 3).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow, 3)
' start to 1st break
ThisWorkbook.Sheets(1).Cells(i, 4).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow, 4)
ThisWorkbook.Sheets(1).Cells(i, 5).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow, 5)
'==2nd new row
ThisWorkbook.Sheets(1).Cells(i, 1).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 1, 1)
ThisWorkbook.Sheets(1).Cells(i, 2).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 1, 2)
ThisWorkbook.Sheets(1).Cells(i, 3).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 1, 3)
' break end to next break start
ThisWorkbook.Sheets(1).Cells(i, 6).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 1, 4)
ThisWorkbook.Sheets(1).Cells(i, 7).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 1, 5)
'==3rd new row
ThisWorkbook.Sheets(1).Cells(i, 1).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 2, 1)
ThisWorkbook.Sheets(1).Cells(i, 2).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 2, 2)
ThisWorkbook.Sheets(1).Cells(i, 3).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 2, 3)
' last shift
ThisWorkbook.Sheets(1).Cells(i, 8).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 2, 4)
ThisWorkbook.Sheets(1).Cells(i, 13).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 2, 5)
NewRow = NewRow + 3
GoTo ReadNextLine
' = = = 3 Breaks = = =
End If
If ThisWorkbook.Sheets(1).Cells(i, 11).Value = vbNullString Then
'==1st new row
ThisWorkbook.Sheets(1).Cells(i, 1).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow, 1)
ThisWorkbook.Sheets(1).Cells(i, 2).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow, 2)
ThisWorkbook.Sheets(1).Cells(i, 3).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow, 3)
' start to 1st break
ThisWorkbook.Sheets(1).Cells(i, 4).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow, 4)
ThisWorkbook.Sheets(1).Cells(i, 5).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow, 5)
'==2nd new row
ThisWorkbook.Sheets(1).Cells(i, 1).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 1, 1)
ThisWorkbook.Sheets(1).Cells(i, 2).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 1, 2)
ThisWorkbook.Sheets(1).Cells(i, 3).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 1, 3)
' break end to next break start
ThisWorkbook.Sheets(1).Cells(i, 6).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 1, 4)
ThisWorkbook.Sheets(1).Cells(i, 7).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 1, 5)
'==3rd new row
ThisWorkbook.Sheets(1).Cells(i, 1).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 2, 1)
ThisWorkbook.Sheets(1).Cells(i, 2).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 2, 2)
ThisWorkbook.Sheets(1).Cells(i, 3).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 2, 3)
' 2nd break end to 3rd break start
ThisWorkbook.Sheets(1).Cells(i, 8).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 2, 4)
ThisWorkbook.Sheets(1).Cells(i, 9).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 2, 5)
'==4th new row
ThisWorkbook.Sheets(1).Cells(i, 1).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 3, 1)
ThisWorkbook.Sheets(1).Cells(i, 2).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 3, 2)
ThisWorkbook.Sheets(1).Cells(i, 3).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 3, 3)
' last shift
ThisWorkbook.Sheets(1).Cells(i, 10).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 3, 4)
ThisWorkbook.Sheets(1).Cells(i, 13).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 3, 5)
NewRow = NewRow + 4
GoTo ReadNextLine
' = = = 4 Breaks = = =
End If
If ThisWorkbook.Sheets(1).Cells(i, 11).Value <> vbNullString Then
'==1st new row
ThisWorkbook.Sheets(1).Cells(i, 1).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow, 1)
ThisWorkbook.Sheets(1).Cells(i, 2).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow, 2)
ThisWorkbook.Sheets(1).Cells(i, 3).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow, 3)
' start to 1st break
ThisWorkbook.Sheets(1).Cells(i, 4).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow, 4)
ThisWorkbook.Sheets(1).Cells(i, 5).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow, 5)
'==2nd new row
ThisWorkbook.Sheets(1).Cells(i, 1).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 1, 1)
ThisWorkbook.Sheets(1).Cells(i, 2).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 1, 2)
ThisWorkbook.Sheets(1).Cells(i, 3).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 1, 3)
' break end to next break start
ThisWorkbook.Sheets(1).Cells(i, 6).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 1, 4)
ThisWorkbook.Sheets(1).Cells(i, 7).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 1, 5)
'==3rd new row
ThisWorkbook.Sheets(1).Cells(i, 1).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 2, 1)
ThisWorkbook.Sheets(1).Cells(i, 2).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 2, 2)
ThisWorkbook.Sheets(1).Cells(i, 3).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 2, 3)
' 2nd break end to 3rd break start
ThisWorkbook.Sheets(1).Cells(i, 8).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 2, 4)
ThisWorkbook.Sheets(1).Cells(i, 9).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 2, 5)
'==4th new row
ThisWorkbook.Sheets(1).Cells(i, 1).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 3, 1)
ThisWorkbook.Sheets(1).Cells(i, 2).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 3, 2)
ThisWorkbook.Sheets(1).Cells(i, 3).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 3, 3)
' 3rd break end to 4th break start
ThisWorkbook.Sheets(1).Cells(i, 10).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 3, 4)
ThisWorkbook.Sheets(1).Cells(i, 11).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 3, 5)
'==5th new row
ThisWorkbook.Sheets(1).Cells(i, 1).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 4, 1)
ThisWorkbook.Sheets(1).Cells(i, 2).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 4, 2)
ThisWorkbook.Sheets(1).Cells(i, 3).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 4, 3)
' last shift
ThisWorkbook.Sheets(1).Cells(i, 12).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 4, 4)
ThisWorkbook.Sheets(1).Cells(i, 13).Copy Destination:=ThisWorkbook.Sheets(2).Cells(NewRow + 4, 5)
NewRow = NewRow + 5
End If
ReadNextLine:
Next i
End Sub