I have a sorted dataset that will be different lengths every day. Need Excel to recognize like invoice numbers and transfer info from the subsequent columns to separate worksheets within the workbook. A sample day's worth of data, in columns J:O of Sheet1 is:


Invoice # Client Employee Time-In Time-Out Hours
209816 A Sharp, John 2:30 PM 8:30 PM 6.00
209816 A Rose, Adam 2:30 PM 8:30 PM 6.00
209817 B Smith, James 4:30 PM 10:30 PM 6.00
209817 B Davis, Frank 4:30 PM 10:30 PM 6.00
209817 B Lyon, Jim 4:30 PM 10:30 PM 6.00
209817 B Coleman, David 4:30 PM 10:30 PM 6.00
209817 B Sanchez, Angel 4:30 PM 10:30 PM 6.00
209817 B Hoff, Mike 4:30 PM 10:30 PM 6.00
209818 C Nguyen, Kim 6:00 PM 10:00 PM 4.00
209818 C Hutch, Mark 5:00 PM 10:00 PM 5.00
209819 D Copeland, Steve 5:00 PM 11:59 PM 7.00
209820 A Ruger, Jeff 5:30 PM 10:45 PM 5.25
209820 A Melend, Veronica 5:30 PM 10:45 PM 5.25
209821 E Finkenbinder, Gene 6:00 PM 10:00 PM 4.00
209822 B Lemmers, Chad 6:30 PM 11:00 PM 4.50
209822 B Coleman, Jody 6:30 PM 12:30 AM 6.00
209822 B Chapell, Richard 6:30 PM 12:30 AM 6.00
209822 B Bass, Kim 6:30 PM 12:30 AM 6.00
209822 B Dodson, Jay 6:30 PM 12:30 AM 6.00
209822 B Hoff, Matt 6:30 PM 12:30 AM 6.00
209822 B Allen, John 6:30 PM 12:30 AM 6.00
209822 B Martinez, Juan 6:30 PM 12:30 AM 6.00

Sheet2 ("Template") is a blank invoice template. This dataset should populate 7 separate worksheets of information, beginning with Sheet3. The code I have tried is:

Sub Populate()

Dim sheetCount As Integer
Dim sheetName As String
Dim wsCount As Integer
Dim rowCount As Integer

With ActiveWorkbook sheetCount = Sheets(1).Range("J2").End(xlDown).Row
For i = 3 To sheetCount Step 1
sheetName = .Sheets(1).Range("K" & i).Value & "_" & .Sheets(1).Range("J" & i).Value
wsCount = .Worksheets.Count .Sheets("Template").Copy After:=Sheets(wsCount)
.Sheets(i).Name = sheetName
For j = 1 To 500 If Sheets(1).Range("J" & i).Value <> Sheets(1).Range("J" & i + 1).Value _
Then Exit For
Next j
Sheets(sheetName).Select Range("E5").Select
ActiveCell.FormulaR1C1 = "=Convert!R[-3]C[6]"
Range("E6").Select
ActiveCell.FormulaR1C1 = "=Convert!R[-4]C[5]"

For k = 11 To 50 Range("A" & k).Select
ActiveCell.FormulaR1C1 = "=Convert!R[-9]C[11]"
Range("B" & k).Select ActiveCell.FormulaR1C1 = "=CONCATENATE(Convert!R[-9]C[11],"" - "",Convert!R[-9]C[12])"
Range("C" & k).Select ActiveCell.FormulaR1C1 = "=Convert!R[-9]C[12]"
Next k

rowCount = Range("C500").End(xlUp).Row + 1
Range("C" & rowCount).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Formula = "=SUM(C11:C" & rowCount - 1 & ")"
Selection.Font.Bold = True
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "Total Hours"
Selection.Font.Bold = True
Next
End With
End Sub