Create Summary Sheet Sort Data VBA

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

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

One thought on “Create Summary Sheet Sort Data VBA”

  1. 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

Leave a Reply

Your email address will not be published. Required fields are marked *