How to create a summary sheet from multiple worksheets and sort data in the summary sheet using VBA.
Watch the training video:

Watch this video on YouTube.

First we generate the summary worksheet using a ‘for next’ loop:
Sub generateSummary()
Dim lastRow As Long
Dim ws As Worksheet
lastRow = Sheets(“summary”).Range(“A” & Rows.Count).End(xlUp).Row
Sheets(“summary”).Range(“A2:B” & lastRow).ClearContents
Sheets(“summary”).Range(“A1”) = “Name”
Sheets(“summary”).Range(“B1”) = “Attendance”
For Each ws In Sheets
If ws.Name <> “summary” Then
Sheets(“summary”).Range(“A” & Rows.Count).End(xlUp).Offset(1, 0).Value = ws.Name
Sheets(“summary”).Range(“A” & Rows.Count).End(xlUp).Offset(0, 1).Value = ws.Range(“B25”).Value
End If
Next ws

End Sub

Next we sort the data:
Sub sortSummarySheet()
Dim lastRow As Long
lastRow = Sheets(“summary”).Range(“A” & Rows.Count).End(xlUp).Row
Range(“A2:B” & lastRow).Select
ActiveWorkbook.Worksheets(“summary”).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(“summary”).Sort.SortFields.Add Key:=Range(“A2”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets(“summary”).Sort
.SetRange Range(“A2:B” & lastRow)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets(“summary”).Select
Sheets(“summary”).Range(“C1”).Select
End Sub

Since the above code may look daunting, we create the same code by recording a macro. Now we see how easy it is to understand the complete VBA code used for sorting the data:
Sub sortData()
‘
‘ sortData Macro
‘ sort summary sheet
‘

‘
ActiveWorkbook.Worksheets(“summary”).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(“summary”).Sort.SortFields.Add Key:=Range(“A2”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(“summary”).Sort
.SetRange Range(“A2:B11”)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub