Excel VBA for Sorting

[JeenLeen]

I've made some VBA code to look at an Excel survey and pull relevant info into a newly created Excel tab as a e-mail list/list for a sign-in sheet.
That part I have figured out.

I also want to sort it by one of the columns in the new Excel sheet. I can't get it to work, neither by code I've found online or by actually doing the sort feature while recording it as a macro, then copying & pasting the VBA code that macro generated. Currently I just have comments as to how to use the Sort feature.

Can someone supply me how to sort all the cells in a sheet by the values in the first row (A1 is header)?



Sub CreateMailList()
'this creates an Excel sheet called MailList

Dim ws As String
ws = ActiveSheet.Name

Dim NumRows As Long ', NumCols As Long
Dim curRowWS As Long, curRowML As Long
Dim SchoolDistrictCol As Long, RegionCol As Long
Dim Title1 As Long, FName1 As Long, LName1 As Long, Pos1 As Long, Email1 As Long
Dim Title2 As Long, FName2 As Long, LName2 As Long, Pos2 As Long, Email2 As Long

NumRows = 1 'sets NumRows1 equal to number of entries in source sheet
Do While Sheets(ws).Cells(NumRows, 1) <> ""
NumRows = NumRows + 1
Loop

Sheets.Add.Name = "MailList" 'creates sheet for mailling list

'assigns the column in the source file for each desired field
'this program assumes the survey results will not change year-to-year, and thus these are static
SchoolDistrictCol = 2 'B
RegionCol = 3 'C
Title1 = 4
FName1 = 5
LName1 = 6
Pos1 = 7
Email1 = 14
Title2 = 16
FName2 = 17
LName2 = 18
Email2 = 19
Pos2 = 20

Sheets("MailList").Cells(1, 1) = "School District" 'all columns 'NOTE: sort by this
Sheets("MailList").Cells(1, 2) = "Region"
Sheets("MailList").Cells(1, 3) = "Title"
Sheets("MailList").Cells(1, 4) = "First Name"
Sheets("MailList").Cells(1, 5) = "Last Name"
Sheets("MailList").Cells(1, 6) = "E-mail"
Sheets("MailList").Cells(1, 7) = "Position"
Sheets("MailList").Cells(1, 8) = "1 = Respondant; 2 = Colleague"

curRowML = 2
curRowWS = 2
Do While curRowWS < NumRows
Sheets("MailList").Cells(curRowML, 1) = Sheets(ws).Cells(curRowWS, SchoolDistrictCol)
Sheets("MailList").Cells(curRowML, 2) = Sheets(ws).Cells(curRowWS, RegionCol)
Sheets("MailList").Cells(curRowML, 3) = Sheets(ws).Cells(curRowWS, Title1)
Sheets("MailList").Cells(curRowML, 4) = Sheets(ws).Cells(curRowWS, FName1)
Sheets("MailList").Cells(curRowML, 5) = Sheets(ws).Cells(curRowWS, LName1)
Sheets("MailList").Cells(curRowML, 6) = Sheets(ws).Cells(curRowWS, Email1)
Sheets("MailList").Cells(curRowML, 7) = Sheets(ws).Cells(curRowWS, Pos1)
Sheets("MailList").Cells(curRowML, 8) = 1
If Sheets(ws).Cells(curRowWS, 15) = "Yes" Then 'if bringing a colleague
curRowML = curRowML + 1
Sheets("MailList").Cells(curRowML, 1) = Sheets(ws).Cells(curRowWS, SchoolDistrictCol)
Sheets("MailList").Cells(curRowML, 2) = Sheets(ws).Cells(curRowWS, RegionCol)
Sheets("MailList").Cells(curRowML, 3) = Sheets(ws).Cells(curRowWS, Title2)
Sheets("MailList").Cells(curRowML, 4) = Sheets(ws).Cells(curRowWS, FName2)
Sheets("MailList").Cells(curRowML, 5) = Sheets(ws).Cells(curRowWS, LName2)
Sheets("MailList").Cells(curRowML, 6) = Sheets(ws).Cells(curRowWS, Email2)
Sheets("MailList").Cells(curRowML, 7) = Sheets(ws).Cells(curRowWS, Pos2)
Sheets("MailList").Cells(curRowML, 8) = 2
End If
curRowWS = curRowWS + 1
curRowML = curRowML + 1
Loop

'to Sort:
'1. Select All (Ctrl+A)
'2. in MailList tab, go to Data --> Sort
'3. Make sure 'My data has headers' is checked. In 'Sort by', select School District or Region, as desired
'4. Hit Okay

End Sub