Hello All. I have a data file with the following data in rows & columns that
comes out of my system like this:

A B C D E F H
I
Stu# Advisor Lname Fname Grade Mbrship Term Mark
1000 Name Smith Jim 11 Math 001 Sem1
A
1000 Name Smith Jim 11 Math 002 Sem2
B+
1001 Name Doe John 10 Science 003 Progess2 C-
1001 Name Doe John 10 Science 003 Progress3 B

I need the data to be in one record instead of in multiple rows like this:

A B C D E F H
I J K
Stu# Advisor Lname Fname Grade Mbrship Prg2 Prg3 Sem1 Sem2
1000 Name Smith Jim 11 Math
A B+
1001 Name Doe John 10 Science C- B

And so forth. The terms can include the following but may not always be in
my data source based on the time of school year the report is being run. So
the columns H and may be more or less each time the report is run.

I tried in my futile attempt to create the code to do this using visual
basic. Can someone help me figure what is wrong with this code? Thanks in
advance for your time:

Sub FormatList()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim oRow As Long
Dim res As Variant

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

With CurWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "H").End(xlUp).Row

'sort original range by Id, name, period
With .Range("a1:h" & LastRow)
.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Key2:=.Columns(3), Order2:=xlAscending, _
Header:=xlYes
End With

'Get a list of unique class students
.Range("a1:a" & LastRow).AdvancedFilter _
action:=xlFilterCopy, unique:=True,
copytorange:=NewWks.Range("A1")
End With

With NewWks
With .Range("a:a")
.Sort Key1:=.Columns(1), Order1:=xlAscending, Header:=xlYes
End With

.Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Copy
.Range("e1").PasteSpecial Transpose:=True
.Range("a:c").Clear
End With

With CurWks
oRow = 1
For iRow = FirstRow To LastRow
If .Cells(iRow, "a").Value < .Cells(iRow - 1, "a").Value Then

oRow = oRow + 1
NewWks.Cells(oRow, "C").Value = .Cells(iRow, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(iRow, "D").Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, "E").Value
NewWks.Cells(oRow, "H").Value = .Cells(iRow, "A").Value
Else

End If
res = Application.Match(.Cells(iRow, "h").Value,
NewWks.Rows(1), 0)
If IsError(res) Then

MsgBox "Error with row: " & iRow
Else

NewWks.Cells(oRow, res).Value = .Cells(iRow, "i").Value
End If
Next iRow
End With

NewWks.UsedRange.Columns.AutoFit

End Sub