thanks a million Tom. I am getting syntax error on the
modified codes, which is pointing at these statements:

& _
",""Vacation""," & rng1.Offset(0,
4).Address & ")"

and

& _
",""Sick""," & rng1.Offset(0,
4).Address & ")"

Sub ProcessData()
Dim cnt As Long, cnt1 As Long
Dim c As Range
Dim firstAddress As String
Dim rngStart As Range

With Worksheets(1).Columns(1)
Set rngStart = .Cells(1, 1)
Set c = .Find("Total", _
After:=Worksheets(1).Cells(Rows.Count, 1), _
Lookat:=xlPart, LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not c Is Nothing Then
cnt = 1
firstAddress = c.Address
Do
If cnt Mod 5 = 0 Then
cnt1 = Application.Round(cnt / 5, 0)
c.Offset(1, 0).Resize(5).EntireRow.Insert
c.Value = "Total" & cnt1
c.Offset(1, 0).Value = "Vacation" & cnt1
c.Offset(2, 0).Value = "Sick" & cnt1
Set rng1 = Worksheets(1).Range(rngStart,
c.Offset(-1, 0))
c.Offset(1, 1).Formula = "=Sumif(" &
rng1.Offset(0, 2).Address
& _
",""Vacation""," & rng1.Offset(0,
4).Address & ")"
c.Offset(1, 1).BorderAround Weight:=xlMedium
c.Offset(2, 1).Formula = "=Sumif(" &
rng1.Offset(0, 2).Address
& _
",""Sick""," & rng1.Offset(0,
4).Address & ")"
c.Offset(2, 1).BorderAround Weight:=xlMedium
Set rngStart = c.Offset(1, 0)
End If
Set c = .FindNext(c)
cnt = cnt + 1
Loop While Not c Is Nothing And c.Address <
firstAddress
End If
End With
End Sub

It was the word wrap. the macro works perfectly. Thanks a
million
-----Original Message-----
thanks a million Tom. I am getting syntax error on the
modified codes, which is pointing at these statements:

& _
",""Vacation""," & rng1.Offset(0,
4).Address & ")"

and

& _
",""Sick""," & rng1.Offset(0,
4).Address & ")"

Sub ProcessData()
Dim cnt As Long, cnt1 As Long
Dim c As Range
Dim firstAddress As String
Dim rngStart As Range

With Worksheets(1).Columns(1)
Set rngStart = .Cells(1, 1)
Set c = .Find("Total", _
After:=Worksheets(1).Cells(Rows.Count, 1), _
Lookat:=xlPart, LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not c Is Nothing Then
cnt = 1
firstAddress = c.Address
Do
If cnt Mod 5 = 0 Then
cnt1 = Application.Round(cnt / 5, 0)
c.Offset(1, 0).Resize(5).EntireRow.Insert
c.Value = "Total" & cnt1
c.Offset(1, 0).Value = "Vacation" & cnt1
c.Offset(2, 0).Value = "Sick" & cnt1
Set rng1 = Worksheets(1).Range(rngStart,
c.Offset(-1, 0))
c.Offset(1, 1).Formula = "=Sumif(" &
rng1.Offset(0, 2).Address
& _
",""Vacation""," & rng1.Offset(0,
4).Address & ")"
c.Offset(1, 1).BorderAround Weight:=xlMedium
c.Offset(2, 1).Formula = "=Sumif(" &
rng1.Offset(0, 2).Address
& _
",""Sick""," & rng1.Offset(0,
4).Address & ")"
c.Offset(2, 1).BorderAround Weight:=xlMedium
Set rngStart = c.Offset(1, 0)
End If
Set c = .FindNext(c)
cnt = cnt + 1
Loop While Not c Is Nothing And c.Address <
firstAddress
End If
End With
End Sub

.