Ask a question Report

To prepare Attendence Report with Excel sheet [Solved]

sneha - Latest answer on Sep 23, 2010 4:50pm BST

Hello,
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
sneha

Answer

+34

Jimmy Sep 6, 2008 2:18pm BST

' 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

Add comment

Jc - Nov 17, 2008 10:03pm GMT

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

timeguru - Feb 3, 2009 2:25am GMT

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

Answer

+21

Anonymous User Nov 7, 2008 2:44am GMT

Hi Sneha i am send the Attandence Excel sheet with this attachment so pls keep it this sheet and prepare attandence reports in own circle

Thanks & Regards
Lalit Rawat

Add comment

Nasir Khan - May 7, 2009 9:57am BST

Dear Friend,
can you send mer attendance report in my email I.D

thanks in Advance,

timeguru - May 7, 2009 10:56pm BST

Hope this helps.

pedo - Jun 9, 2009 1:14pm BST

could you send me the attendance sheet

timeguru - Jun 16, 2009 5:12am BST

Hi Pedo do you still need a copy of the spreadsheet if you put your email address here i can send it to you, but I dont subscribe anymore therefore can not see your email address. Still willing to share and help out if I can

richa - Aug 8, 2009 12:29pm BST

please provide the attendance sheet so that I can also use in my office as doing it manually is very tedious and time consuming

Answer

GSNEHA Sep 8, 2008 6:08pm BST

The code worked like a charm... no problem at all... thanks a lot...

Add comment

View all 9 comments

Timeguru - May 13, 2009 3:06am BST

Hi Kat need the email address to send it

Kat - May 13, 2009 3:22am BST

Thanks a lot for your prompt reply!

Wishes!

Kat

timegure - May 13, 2009 6:56am BST

Hi Kat

still can not see your email address mine is drwhoandk9@gmail.com then if you send me your address I can attach the file

Murugan › Kat - May 14, 2009 5:54pm BST

Hi Kat,

It looks like you got the Attendance Template. Could you send the file to my email id...murugan.guna@gmail.com

Thanks in advance..

timeguru - May 14, 2009 10:00pm BST

Murugan have sent you the file from work cheers

Ron

Murugan - May 15, 2009 7:54pm BST

Hi Ron,

Just gone through the 2 excel sheets - of which one worked like a charm and it just puts me on "awe" with the design you mapped. However - I tried to add a new employee following the step 2 process which says "Click on the Create Wages Pay Sheet button. If you have new employees select update team list button", which I dont see its working...Could you help in this regard...

Thank you & Agog :-)
Murugan

timeguru - May 16, 2009 10:41am BST

Hi Murugan

It sounds like something is wrong with the sheet. What should happen is that it will create worksheets for employees in the hidden rows. I have set them up as a family named Adams. Unhide the rows from 37 down and add the names of your employees in place of the addams.

This hidden area also has details of work areas you may need or not.

If the create new worksheet button is not creating a sheet for each employee automatically there may be a problem with both sheets I sent. I will need to check my virus check to make sure it is not stripping macros. Let me know if you have created new worksheet tabs once you put your employe names in the hidden fields.
I have not developed macro for adding, deleting employees from hidden field reference yet.

If not I will need to recreate for you and ensure my virus program is not stripping it. Kat is your working

cheers

Answer

gsneha Sep 7, 2008 4:13pm BST

Thanks for your immediate reply. I will go back to my computer on monday and check the code thanks a lot ......
Sneha

Add comment

View all 7 comments

timeguru - Aug 11, 2009 10:47pm BST

Hi

send me your email address and i will send you a copy of the sheet I have developed using some of the code from here plus other functions.

Ron

aman - Feb 4, 2010 3:54pm GMT

Hi, I m amandeep ,,,i dnt know how to work on excel.So plz send the excel file.

timeguru - Feb 4, 2010 8:20pm GMT

Hi aman I need an email address to send it too. I can not attach file through here. I have never been ble to work it out. Otherwise I would just leave a copy on the site.

Ron

Fahee › pavan - Oct 19, 2009 12:02pm BST

Hi, how are you hope you ll b fine and great , i m doing job in UAE in accounts depptt.so i want to know how can i put formula or daily or monthly employee statuse of attendance sheet....leave,sunday.etc..

time - Oct 19, 2009 8:47pm BST

Fahee
send me an email addressto send file too. I am unable to attach thru site.

ron

Answer

rachmad Jan 4, 2009 10:12pm GMT

Dear Sneha,
I found my self facing a same interest with you, would you send me the solution from your question?
I'll appreciate it.

Thanks and regards,
Rachmad Hidayat

Add comment

Answer

sudhir Feb 12, 2009 7:20am GMT

hi friend,

lease can you send the file at my email id .this will be wonderful for you. sorry for me. so please send the excel file .

Add comment

timeguru - Feb 12, 2009 8:12pm GMT

Hi mate

Thanks very much for the reply. I have managered to get it all to work now thanks

Ron

timeguru - May 7, 2009 10:59pm BST

send me email address to send file I dont seem to be able to attach it

Answer

timeguru Jul 22, 2009 4:47am BST

Hi Salma

Sent you the spreadsheet with all of the macros hope it helped out with your problems.

Ron

Add comment

timeguru - Jul 29, 2010 5:55pm BST

Hi Santhu
I sent you the file the other day I only just realised it may have been a protected copy. Will send you a new copy shortly.
ron

Answer

akshay Sep 23, 2010 4:50pm BST

how to prepare salary sheet in ms-excel

Add comment

Answer

fufi Aug 30, 2009 8:19am BST

I would love it if someone forwards me this excel sheet.
Thank you!

Add comment

timeguru - Aug 30, 2009 10:02pm BST

Hi send me your email address and I will forward to you I can not seem to email from the site.

Ron

Answer

timeguru 1Posts Tuesday January 27, 2009Registration date Oct 14, 2009 10:07pm BST

James I have sent you copy of timesheet. It would be nice if you and others who I have sent the sheet to could send me a copy of the sheet you all now use. This way I can draw together the numerous various and try and combine some of the features for redistribution of an improved timesheet.

Ron

Add comment

Zaxter - Oct 22, 2009 2:01pm BST

Hello,

I have an attendance sheet wherein the rows occupy the names of people while columns occupy the days of the month. I wanted to calculate the percentage of attendance at the end of the column for each person.
for eg:
1 2 3 4 5 Percentage of attendance
A 75%
B 65%
C
B

Could you please help me out with this... My id is mailzaxter@gmail.com

Thanks a lot for your help

Answer

Orkt 6Posts Friday May 29, 2009Registration date Jun 1, 2009 12:00am BST

can you please sen attendent sheet to my e-mail also

thanks

Add comment

Timeguru - Jun 1, 2009 12:57am BST

I can not attach the sheet thru the site unfortunaley. If you have an email address i can send it to please advise address an I will organise. otherwise one of the people who has obtained from me may be able to send it too you.

Ron

Timeguru - Jun 1, 2009 12:57am BST

Reply to this topic

Ask a question

Not a member yet?

sign-up, it takes less than a minute and it's free!

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

To prepare Attendence Report with Excel sheet [Solved]

Not a member yet? sign-up, it takes less than a minute and it's free!

Not a member yet?

sign-up, it takes less than a minute and it's free!