Excel VBA, Loop

Question

I'm pretty new to VBA and cannot get a loop work.

Column J contains certain ID numbers. For each ID number I have several rows displayed in my database. I sorted my database on column J (ascending order). I would like to filter for all the values in columns J and copy all the rows for each ID number into a new sheet what I want to rename as the ID number. Here is the macro but the loop doesn't work (Run-time error 13, Type mismatch, error in the following code row: For i = 45 To LastEmpNo)

Any help would be welcomed. Thanks

Sub Macro3()

Dim MyDataFirstCell
Dim MyDataLastCell
Dim EmpNoStart
Dim EmpNoFinish
Dim i As Integer

Range("A1").Select
MyDataFirstCell = ActiveCell.Address
Selection.End(xlDown).Select
Selection.End(xlToRight).Select
MyDataLastCell = ActiveCell.Address 

Range("J2").Select
EmpNoStart = ActiveCell.Address
Selection.End(xlDown).Select
EmpNoFinish = ActiveCell.Address

Range(EmpNoStart & ":" & EmpNoFinish).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns _
    ("K:K"), Unique:=True

Dim FirstEmpNo
Dim LastEmpNo

Range("K2").Select
FirstEmpNo = ActiveCell.Address
Selection.End(xlDown).Select
LastEmpNo = ActiveCell.Address

For i = FirstEmpNo To LastEmpNo

    Range("J1").Select
    Selection.AutoFilter
    ActiveSheet.Range(MyDataFirstCell & ":" & MyDataLastCell).AutoFilter Field:=10, Criteria1:=i
    Rows("1:1").Select
    Range("C1").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    Columns("B:B").EntireColumn.AutoFit
    Cells.Select
    Cells.EntireColumn.AutoFit
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = i

Next i


End Sub

Answer 1

You cannot iterate from one string value to another. The "For" loop is expecting two numeric values.

?activecell.Address
$C$9
?activecell.Row
 9 

Therefore:

FirstEmpNo = ActiveCell.Row
Selection.End(xlDown).Select
LastEmpNo = ActiveCell.Row