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