VBA Help one sheet data spread in multisheets by employee code

Dear Sir,

Please find the attached file ,

In this workbook 1 sheet is a data entry sheet . you will find in column 3 as a employee code , and other sheets name defined as employee code :

when i run macro then data spread to other sheets by employee code .

But problem is that ... when i click to run macro sheet name : 1,2,3 rows increasing . it should not to be .

so can u help me .

Try this code

Sub copy_data()
Dim i As Long, lrow As Long
Dim sname As String

Application.ScreenUpdating = False

For i = 1 To Worksheets.Count
    With Worksheets(i)
        If .Name <> "Data entry" Then
            lrow = .Range("A" & .Rows.Count).End(xlUp).Row
            If lrow > 1 Then .Range("A2:G" & lrow).ClearContents
        End If
    End With
Next i

With Worksheets("Data entry")
    lrow = .Range("B" & .Rows.Count).End(xlUp).Row
    For i = 2 To lrow
        sname = .Range("C" & i).Value
        If Not Evaluate("ISREF('" & sname & "'!A1)") Then
            Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sname
            .Rows("1:1").Copy Worksheets(sname).Range("A1")
        End If
        .Range("A" & i & ":G" & i).Copy Worksheets(sname).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Next i
End With

MsgBox "Data transferred"

Application.ScreenUpdating = True

End Sub

Thank you so much
arlu1201 & also thanks to VBA forum team .

For those who prefer to use VBA:

Sub snb()
  sn = Sheets("Data entry").Cells(1).CurrentRegion

  For j = 2 To UBound(sn)
    If sn(j, 3) <> "" Then Sheets(Format(sn(j, 3))).Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, UBound(sn, 2)) = Application.Index(sn, j)
  Next
End Sub