To prepare Attendence Report with Excel sheet

sneha - Sep 5, 2008 at 04:47 PM
 akshay - Sep 23, 2010 at 11:50 AM
I will describe what I want to do .

I have 60 residents working in our department. They attend morning report everyday. So I have prepared excel sheet( Named Attendance Table) which has column A1:A60 with names of residents B1:B60 with percentage of attendance for one month.

My job is to prepare Attendance report for each resident.

I have prepared Template for attendance report.

Now I want to run a Macro which will do 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 till it reaches the empty cell

Can anyone help me write the macro.

Thank You

12 replies

' 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
Jimmy your response to the person from this post seem very close to helping me with my issue. We are creating a "list" using excel. I need to create a macro to do the following:

1. Add a new sheet from an original or master( ideally by pressing a button or key combination). The sheet will be named numerically in sequence.
2. Create cell references on a "Project" page refering to the new sheet created.

example. Create sheet "51" ( or the next number in the sequence),
Copy the information in cell '51'!$B$1 to the next available cell in Column B on the "project" sheet.
Copy hte information in cell '51'!$B$3 to the next available cell in column C on the "project" sheet.

and so forth until you get to column G
Hi Jimmy
Mate I got your code to work and create the named worksheets populate the appropriate values into them however. I have a tempalte say called worksheet1. When I run the macro it creates all of the worksheets from the referenced list in worksheet1. So I have six new worksheets all with the employee name and staff numbers in them and the worksheet named after the corresponding employees. Hw ever it does not take the template across I only have the name where it should go and staff number but not the layout of the tempalte. Can you help