Try out the below macro in a fresh workbook with your data in ColA/ColC
starting from row 1..


Sub MyMacro()
Dim ws As Worksheet, ws1 As Worksheet, lngRow As Long, lngSrow As Long
Set ws = ActiveSheet: Set ws1 = ws
For lngRow = 1 To ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
If UCase(Right(ws.Range("A" & lngRow), 5)) = " DEPT" Then
If lngSrow = 0 Then
lngSrow = lngRow + 1
Else
Set ws1 = Worksheets.Add(After:=ws1)
ws.Range("A" & lngSrow & ":C" & lngRow - 1).Copy ws1.Range("A1")
ws1.Name = ws.Range("A" & lngSrow - 1): lngSrow = lngRow + 1
End If
End If
Next
Set ws1 = Worksheets.Add(After:=ws1)
ws.Range("A" & lngSrow & ":C" & lngRow - 1).Copy ws1.Range("A1")
ws1.Name = ws.Range("A" & lngSrow - 1)
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Maarkr" wrote:

I have a data dump, and I need to break apart the column every time a value
(*dept*) shows in the first column:

finance dept
bob... ssn id-no
other data
sally...
(list of 15 more names)
mfg dept
joe...
bill...
(list of 30 more names)
payroll dept
lucie...
(list of 4 more names)
more depts with names...

so i need to loop thru the first column, and have each *dept* in it's own
new column or worksheet so I can print out each dept and other column info.
i've had suggestions to use 'split', but I'm not using the split function -
I need to break out each group of depts.