Step 5 is done by "sub sort". This is where I am getting a compile error "Sub or function not defined". I am new to excel vba, I am not able to narrow down the issue further. Could anyone help me on this issue please? TIA.

This is an outright shot in the dark, but I made these changes on the Sort sub. Does not error and produces a new worksheet each time I run it in a workbook with no data.

The name of the sub is purposefully change, as it produced an error. I presume "sort" is a reserved word for Excel.

Change the sheet names back to suit your workbook.

Regards,
Howard

Sub Mysort()

Dim x As Integer
Dim y As Integer
Dim erow As Range

y = 10
x = 2
Sheets.Add after:=Sheets(Sheets.Count)
Do While Cells(1, x) < ""
If Cells(1, x) = "##RETENTION_TIME" Then
Sheets("Sheet2").cell(1, x).Copy
Sheets("sheet1").Activate
Set erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste destindation:=Worksheets("sheet1").Range("erow" & ":" & "A" & "y")
End If
Sheets("Sheet2").Activate
x = x + 1
Loop

End Sub