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
Dear Mr. Takyar:
I am learning to code with VBA thanks to your You Tube tutorials! I have created a couple of macros to increase efficiency at work and would like to continue. Currently, I envision a macro to generate a summary report based on review findings. The review has multiple categories with data validation choices. I would like to use (if it is best) an if loop, i.e., If cell Imaged and N/A then copy. It must also copy the name of the user and the doc type from the header.
I am not able to find any code online that explains how to capture the header directly above and the name to the left. I have copied examples of the data & report format for further clarification. Also, note that the Relationship & Type is a filter so that the number of users will be specific to each file.
I would greatly appreciate any assistance you can provide.
Thank you so much,
Donna
Relationship & Type
User 1 Name Doc 1 Doc 2 Doc 3 Doc 4
User 1 Not Imaged Imaged Imaged Imaged
User 2 Name Doc 1 Doc 2 Doc 3 Doc 4
User 2 Imaged Incomplete Not Signed Imaged
User 3 Name Doc 1 Doc 2 Doc 3 Doc 4
User 3 Imaged Imaged Imaged Imaged
User 4 Name Doc 1 Doc 2 Doc 3 Doc 4
User 4 Imaged Not Imaged Imaged Imaged
User 5 Name Doc 1 Doc 2 Doc 3 Doc 4
User 5 Not Imaged Not Imaged Not Imaged Not Imaged
Summary Report
Name Items Not Imaged Items Incomplete Items Not Signed
User 1
User 2
User 3