summarizing multiple sheets

**MickeyP** · 10-04-2007, 08:50 AM

Hi

I've got myself all confused on what to do.

I have a workbook, with lots of sheets all named after employees, I want to create a paysheet to send to my payroll company.

I have created a list of all sheets, and would like to create a formula, to look at the name in the cell, go to that named sheet and come back with a value from 1 specified cell.

I've tried pivot table but cannot work out how to get different info from multiple pages, most of the formulas want to look at arrays, my head is hurting.

If anyone can help then please let me know. I can always attach the form I have created for people to have a look at.

Thanks

MickeyP

**wmorrison49** · 10-04-2007, 09:12 AM

Why don't you attach the file and I will take a look at it. Also, please include some specifics as to what you want to go where and just a step by step process of what you are looking for.

**MickeyP** · 10-04-2007, 09:38 AM

Hi

Thanks for looking, I have put a little message box on the last tab. I hope you can help. This is a reduced version as I had 72 tabs before.

**wmorrison49** · 10-04-2007, 09:50 AM

I think I can do this. I need a little help though, since I am not familiar with your spreadsheet. You requested Employee #, Rate of Pay, Contract Hours, Total Hours worked, Total Holiday, Total Sick, Total Absence, and Bank Holiday. Can you tell me which cells each of these can be found in on the employee sheets? That would make sure that I am pulling the right data. Thx

**MickeyP** · 10-04-2007, 09:54 AM

Employee Number: C9
Rate of Pay: D6
Contract hours: M8
Total Hours worked:Column Q
Holidays:Column T
Sick Pay: Column V
Absence:Column X
Bank Holiday: Column y

thanks

**wmorrison49** · 10-04-2007, 10:20 AM

Alright, try running this macro and see if it works for you. It was a little hard to tell if it was working completely because the workbook you attached is mostly zeroes and blanks, but I think it is doing what it is supposed to. Give it a try and please let me know if it worked or not.

Public Sub RetrieveData()
Dim EmployeeName As String
Dim EmployeeNumber As String
Dim RateofPay As String
Dim ContractHours As String
Dim Week As String
Dim TotalHoursWorked As String
Dim Holidays As String
Dim SickPay As String
Dim Absence As String
Dim BankHoliday As String
Sheets("Ceridian").Activate
Week = Range("B4")
Range("A9").Select
Do Until ActiveCell = ""
EmployeeName = ActiveCell
Sheets(EmployeeName).Activate
EmployeeNumber = Range("C9")
RateofPay = Range("D6")
ContractHours = Range("M8")
Range("A15").Select
Do Until ActiveCell = ""
If ActiveCell = Week Then
TotalHoursWorked = ActiveCell.Offset(0, 16)
Holidays = ActiveCell.Offset(0, 19)
SickPay = ActiveCell.Offset(0, 21)
Absence = ActiveCell.Offset(0, 23)
BankHoliday = ActiveCell.Offset(0, 24)
Exit Do
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Sheets("Ceridian").Activate
ActiveCell.Offset(0, 1) = EmployeeNumer
ActiveCell.Offset(0, 2) = RateofPay
ActiveCell.Offset(0, 3) = ContractHours
ActiveCell.Offset(0, 4) = TotalHoursWorked
ActiveCell.Offset(0, 5) = Holidays
ActiveCell.Offset(0, 6) = SickPay
ActiveCell.Offset(0, 7) = Absence
ActiveCell.Offset(0, 8) = BankHoliday
ActiveCell.Offset(1, 0).Select
Loop

End Sub

Also, the employee names on the sheet "Ceridian" must be the names of the sheets EXACTLY. I noticed there were asterisks by the names of the employees on the Ceridian sheet and I got rid of those. Otherwise it won't be able to match them up, so you will want to delete those.
Hope it works!