Weekly Time Sheet Consolidate
 

Good morning,
I am having a problem with time sheet reporting. We have a weekly timesheet
which hourly employees complete every week. Most have been saving EACH
timesheet. That is 52 timesheet tabs.
What I am trying to accomplish is this:
Use the time sheet, and have the TOTALS of each column transfer to a single
worksheet. Example of columns in weekly timesheet:
DAte Day of Week Reg OT Sick Vac Total Hours Lunch
Time Used

The date column is filled with date beginning on a Sunday, ending Saturday.
There are formulas to calculate the total hours.

The last row of the weekly timesheet has TOTALS for the week:
w/e Date Reg OT Sick Vac Total Hours LunchTime
Used
12/19 40 5 45
2.5

I am trying to get the last row (totals) to transfer to a separate sheet
using a formula. The sheet is what I call the W/E Totals.
This Totals sheet has the same columns as the weekly timesheet. So, for
instance, for W/E 12/19, this sheet would show the above total weekly hours.
When I complete next week's Timesheet, the dates are changed on that single
timesheet to reflect the new w/e date. I want the totals of that weekly
timesheet to transfer to the TOTALS worksheet. So the resulting time sheet
would have cumulative information from each WEEKLY TIME SHEET, for example
w/e Date Reg OT Sick Vac Total Hours LunchTime
Used
12/05 40 5 45
2.5

12/12 40 2 2
2.5

12/19 40 0 42
2.5


I've tried this formula =IF($A3='Weekly Time Sheet'!$C$4,'Weekly Time
Sheet'!C$17)but it doesn't work....it then gives a "FALSE" for the previous
week's information. '$A3 being the week ending date on the TOTALS
worksheet (same week endings as on the weekly Time sheet). 'Weekly Time
Sheet' being the sheet where daily hours are entered on weekly basis. $C$4
is the week ending date on the weekly timesheet. C$17 being the Totals row
of the weekly time sheet; C$17 is the Regular Hours Column; D$18 is the OT
column, etc., etc.....

Sorry for so much information. Looking forward to a formula that will work.
Sincerely,

FMW

Thank you for your help. FMW

Weekly Time Sheet Consolidate
 

Try this:
Sub ListData10()
Dim ws As Worksheet
Dim rCopy As Range
Dim rDest As Range

Set rDest = ActiveWorkbook.Worksheets("Summary").Range("B3")
For Each ws In ActiveWorkbook.Worksheets
If ws.Name < "Summary" Then
rDest.Offset(0, -1).Value = ws.Name
With ws.Range("B39:T39")
rDest.Resize(1, .Columns.Count).Value = .Value
End With
Set rDest = rDest.Offset(1, 0)
End If
Next ws
End Sub

It takes all the data in Range("B39:T39"), on each sheet and copies/pasts it
to the sheet named 'Summary'.




--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"FMW" wrote:

Good morning,
I am having a problem with time sheet reporting. We have a weekly timesheet
which hourly employees complete every week. Most have been saving EACH
timesheet. That is 52 timesheet tabs.
What I am trying to accomplish is this:
Use the time sheet, and have the TOTALS of each column transfer to a single
worksheet. Example of columns in weekly timesheet:
DAte Day of Week Reg OT Sick Vac Total Hours Lunch
Time Used

The date column is filled with date beginning on a Sunday, ending Saturday.
There are formulas to calculate the total hours.

The last row of the weekly timesheet has TOTALS for the week:
w/e Date Reg OT Sick Vac Total Hours LunchTime
Used
12/19 40 5 45
2.5

I am trying to get the last row (totals) to transfer to a separate sheet
using a formula. The sheet is what I call the W/E Totals.
This Totals sheet has the same columns as the weekly timesheet. So, for
instance, for W/E 12/19, this sheet would show the above total weekly hours.
When I complete next week's Timesheet, the dates are changed on that single
timesheet to reflect the new w/e date. I want the totals of that weekly
timesheet to transfer to the TOTALS worksheet. So the resulting time sheet
would have cumulative information from each WEEKLY TIME SHEET, for example
w/e Date Reg OT Sick Vac Total Hours LunchTime
Used
12/05 40 5 45
2.5

12/12 40 2 2
2.5

12/19 40 0 42
2.5


I've tried this formula =IF($A3='Weekly Time Sheet'!$C$4,'Weekly Time
Sheet'!C$17)but it doesn't work....it then gives a "FALSE" for the previous
week's information. '$A3 being the week ending date on the TOTALS
worksheet (same week endings as on the weekly Time sheet). 'Weekly Time
Sheet' being the sheet where daily hours are entered on weekly basis. $C$4
is the week ending date on the weekly timesheet. C$17 being the Totals row
of the weekly time sheet; C$17 is the Regular Hours Column; D$18 is the OT
column, etc., etc.....

Sorry for so much information. Looking forward to a formula that will work.
Sincerely,

FMW

Thank you for your help. FMW