To prepare an Attendance Report with Excel sheet

January 2017

The attendance report can be easily prepared in an Excel sheet. There are some methods, which are necessary to follow to arrange Excel sheets. Prepare an Excel sheet showing the percentage of attendees in one month. The user will also need to prepare a template for the attendance report. It would be easier to run it in Macro format. Open a new worksheet that contains the template of the attendance report. It is necessary to fetch the data from the Attendance table worksheet. Prepare columns inserting the data. Now rename the new work sheet. Repeat the process until it reaches the destination that is the empty cell. The Office software is really helpful in this application of attendance preparation.

To prepare an Attendance Report with Excel sheet


I have 60 residents working in our department. They attend morning report every day. So I have prepared an Excel sheet (Named Attendance Table) which has columns A1:A60 with the names of residents B1:B60 with the percentage of attendance for one month. My job is to prepare an Attendance report for each resident. I have prepared a template for the attendance report. Now I want to run a Macro which will do the following things:
1. Open a new worksheet from the template in the workbook which has "Attendance Table worksheet"
2. It should bring the data from "Attendance Table" worksheet
a. From first column A1 and put in Name field of template (new worksheet opened in stage one) (always D6)
b. Bring data from B1 and put in percentage field of template (F10)
c. It should rename the worksheet tab with data from A1 (i.e. name of the resident)
3. It should repeat the step 2 until it reaches the empty cell
Can anyone help me write the macro.?


' 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


Solved by Jimmy


Published by aakai1056. Latest update on November 6, 2016 at 03:15 PM by owilson.
This document, titled "To prepare an Attendance Report with Excel sheet," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (