Cannibalising code

Hi there,

We've received this macro from another Local Authority, that automaticaly formats an excel/csv exported reports from a cr*p Capita-made database.

The report comes out of the database in excel as one long unformatted list, the below code sorts it out into a proper data table.

We would like to tweak the below code so it works with a range of other very similar reports from the same database. However the below code hasn't got many comments so we are a bit confused and would appreciate any pointers.

Cheers
Gareth Thomas
LB Merton



Sub converterThing()

Dim eof As Long
Dim i As Long, start As Long, finish As Long, j As Long
Dim currCode As String
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With ActiveWorkbook.ActiveSheet
' Add column for living in code
.Range("A1").EntireColumn.Insert xlShiftToRight
'where the data ends

eof = .Range("b65536").End(xlUp).Row
'Find first Living in
For i = 1 To eof Step 1
'Get the code
If Left(.Cells(i, 2), 10) & "" = "Living in:" Then
'found the living in:
'get code
currCode = Mid(.Cells(i, 2), 12, Len(.Cells(i, 2)))
start = i + 3
For j = i + 3 To eof Step 1
If Left(.Cells(j, 2), 6) = "Settin" Then
'End is found
finish = j - 1
Exit For
End If
Next j
.Range(.Cells(start, 1), .Cells(finish, 1)) = currCode
i = j - 1
End If
Next i


For i = 1 To eof Step 1
If .Cells(i, 2) = "Name" Then
'found the first row
.AutoFilterMode = False
.Range(.Cells(i, 1), .Cells(eof, 10)).AutoFilter 1, "="
.Range(.Cells(i + 1, 1), .Cells(eof + 1, 1)).EntireRow.Delete
.AutoFilterMode = False
.Range(.Cells(10, 1), .Cells(11, 1)).EntireRow.Delete
.Cells(10, 1) = "Living In"
Exit For
End If

Next i
.Cells.Columns.AutoFit
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub