Excel - - creating an Attendance Report

January 2017

Microsoft Excel is a very useful tool for creating, storing and manipulating data. Excel can be used to create a variety of reports and charts. For example, it can be used to create an attendance report. As part of the first step to create an attendance report, Excel is used to collect the attendance data for people attending work/school/meetings etc. and their percentage of attendance. After that, one has to write a custom macro using a VBA environment, which aggregates the data automatically only for the available range of data without any specific input from the user.


If you want to create an attendance report with Excel, here's a nice example:

Consider that your report will have the following parameters:

You have 60 residents working in your department. They attend morning report everyday. So, I have created an Excel sheet (named Attendance Table) that includes columns A1:A60 with the names of residents B1:B60 with their percentage of attendance for one month.

Once the template for the attendance report is created you want to run a Macro that will do the following things:
  • Launch a new worksheet from the template in the workbook which has "Attendance Table worksheet". It should bring the data from "Attendance Table" worksheet
  • From the first column A1, put in Name field of template (new worksheet opened in stage one) ( Always D6)
  • Bring data from B1 and enter Percentage field of Template (F10)
  • It should change the name of the worksheet tab with Data from A1 (i.e. name of the resident)
  • It should repeat step 2 until it reaches the empty cell


' Attendance Report Subroutine to     
' Open New Worksheet called attendance tqable worksheet
' With data from Attendance Table
' Residents name to D6
' Percentage of attendnace for one month to F10
' Rename sheet to residents name and start again
' For all residents in table

Sub AttendanceReport()
Dim cCell As Object, i As Integer 'Two variables cCell (current Cell) object and i (standard counting integer)
Cells(1, "A").Select Ensure that we start each time at the top of the worksheet
Application.ScreenUpdating = False 'Turn of screen updates whilst macro is running
For Each cCell In Range(Cells(1, "A"), Cells(1, "A").End(xlDown)) 'Will run our code through each cell with text
Set NewSheet = Sheets.Add(Type:=xlWorksheet) 'Add new worksheet
NewSheet.Name = "Attendance Table Worksheet" 'rename worksheet
Sheets("Attendance Table Worksheet").Cells(6, "D").Value = cCell.Value 'put residents name is cell D6
Sheets("Attendance Table Worksheet").Cells(10, "F").Value = cCell.Offset(0, 1).Value 'put residence attednance into F10
Sheets("Attendance table worksheet").Name = cCell.Value
Next cCell
End Sub

Thanks to Jimmy for this tip.


Published by aakai1056. Latest update on October 18, 2016 at 05:30 PM by owilson.
This document, titled "Excel - - creating an Attendance Report ," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).