Consolidating Timesheet Data - Help!

Q

qdavhen

New Member
Joined
Feb 16, 2007
Messages
9
Hi, I collect employee times in worksheets. A separate worksheet/workbook per employee. I require a macro to consolidate all of the data from all of the workbooks per employee. For example:

Employee: Fred (Cell A2) Date: 22/02/08 (Cell C2)

(Data starts in Row 11 - Col A - Col G)

Row 11 - Project /Activity Mon Tues Wed Thurs Friday Total
Row 12 - 1234 Project 1 7 7 7 7 0 28
Row 13 - 1236 Project 2 1 1 1 1 8 12
Row 14 - 23 Same, if person is working on more projects
Row 24 - Public Holiday/Annual Leave
Row 25 - Sick

Employee: Barney (Cell A2) Date: 22/02/08 (Cell C2)

(Data starts in Row 11 - Col A - Col G)

Row 11 - Project /Activity Mon Tues Wed Thurs Friday Total
Row 12 - 1234 Project 1 7 7 7 0 20
Row 13 - 1238 Project 3 1 1 1 8 12
Rows 14 - 23 Same, if person is working on more projects but may not be.
Row 24 - Public Holiday/Annual Leave
Row 25 - Sick 8 8


If I have say 20 employees, I would get 20 returns each month. What I need is a macro that will open all the files kept in C:\mydocuments\timesheets and consolidate the employee, date, project/activity and Total from all the sheets so I get a return that looks like:

Fred 22/02/08 1234 Project 1 28
Fred 22/02/08 1236 Project 2 12
Barney 22/02/08 1234 Project 1 20
Barney 22/02/08 1238 Project 3 12
Barney 22/02/08 Sick 8

Can anybody help me with this please. I'm a novice when it comes to Excel!

Thanks

Dave.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Hi
insert these codes in the macro window ( Alt F11)
Sub extract()
Dim a, c, d, x As Integer
Dim f As String
Cells(2, 1).Select
f = Dir("C:\mydocuments\timesheets\" & "*.xls")
Do While Len(f) > 0
ActiveCell.Formula = f
ActiveCell.Offset(1, 0).Select
f = Dir()
Loop
x = Cells(Rows.Count, 1).End(xlUp).Row
d = 2
For a = 2 To x
For c = 11 To 25
Cells(1, 1) = "='C:\mydocuments\timesheets\[" & Cells(a, 1) & "]sheet1'!A2"
Cells(d, 2) = Cells(1, 1)
Cells(1, 1) = "='C:\mydocuments\timesheets\[" & Cells(a, 1) & "]sheet1'!c2"
Cells(d, 3) = Cells(1, 1)
Cells(1, 1) = "='C:\mydocuments\timesheets\[" & Cells(a, 1) & "]sheet1'!A" & c
Cells(d, 4) = Cells(1, 1)
Cells(1, 1) = "='C:\mydocuments\timesheets\[" & Cells(a, 1) & "]sheet1'!G" & c
Cells(d, 5) = Cells(1, 1)
d = d + 1
Next c
d = d + 1
Next a
End Sub
Run the macro. let me know How close it is to the expected output.
ravi
 
Upvote 0
How would code differ if all individual employee worksheets were in same workbook, all trailing the Summary sheet?
 
Upvote 0
Hi
You need to provide the sheet names from outside (I don't know how to retrieve sheetnames from closed file). If sheet names are sheet1,2,3....10, then it can be generated in a loop. for ex:
For z = 1 to 10
Cells(1, 1) = "='C:\mydocuments\timesheets\[" & Cells(a, 1) & "]sheet" & z & "'!A2"
Cells(d, 2) = Cells(1, 1)
next z

if sheet names are a text then

For z = 1 to 10
m = choose(z,"jan","feb","Mar",........,"oct")
Cells(1, 1) = "='C:\mydocuments\timesheets\[" & Cells(a, 1) & "]sheet" & m & "'!A2"
Cells(d, 2) = Cells(1, 1)
next z

Ravi
 
Upvote 0
Hi
On second thought, the code will be much simpler if it is in the same workbook.

Dim a,b,c,d as integer
Worksheets("summary").activate
d= 2
For a = 2 to sheets.count
for b = 1 to 50 ' no of rows
for c = 1 to 8 'no. of columns
Cells(1, 1) = worksheets(a).cells(b,c)
Cells(d,c) = Cells(1, 1)
next c
d= d+1
next b
d= d+1
next a

or if you know the range of data

Dim a , b as integer
Worksheets("summary").activate
For a = 2 to sheets.count
worksheets(a).Range("A1:H50").copy
b=worksheets("summary").cells(rows.count,1).end(xlUP).row
worksheets("summary").cells(b+2,1) = worksheets(a).name
worksheets("summary").Range("B" & b+2).pastespecial
next a

Ravi
 
Upvote 0
Hi Ravishankar,

Thanks for your code, it works, but not 100%! Here is the output (from two sample timesheets).

0
Barney.xls Barney 23/02/2008 Project Total
Fred.xls Barney 39501 ProjectA1 5
Barney 39501 ProjectB2 10
Barney 39501 ProjectB3 15
Barney 39501 0 0
Barney 39501 0 0
Barney 39501 0 0
Barney 39501 0 0
Barney 39501 0 0
Barney 39501 0 0
Barney 39501 0 0
Barney 39501 0 0
Barney 39501 0 0
Barney 39501 0 0
Barney 39501 0 0

Fred 39469 Project Total
Fred 39469 ProjectX1 5
Fred 39469 ProjectX2 10
Fred 39469 ProjectX3 15
Fred 39469 0 0
Fred 39469 0 0
Fred 39469 0 0
Fred 39469 0 0
Fred 39469 0 0
Fred 39469 0 0
Fred 39469 0 0
Fred 39469 0 0
Fred 39469 0 0
Fred 39469 0 0
Fred 39469 0 0

I guess there's a counter in there as it returns 15 rows of data for each timesheet. usually there are 1 or 2 entries at the top of the sheet, and maybe 1 or 2 at the bottom (sick or holiday). It would be good if it could check a row and if it finds a total then it returns the row, and moves to the next one - the last row is always row 25.

Also, in cell A1 it returns a "0" and underneath the names of the spreadsheets it's looked at... not sure if that's intentional, but probably not such a bad thing as I can see if it's used everything in the directory. Thanks again!!

Dave
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Formula Help.
Replies
3
Views
62
Fluff
Fluff
N
  • Locked
  • Question
Data Extract With Multiple OR Criteria
Replies
2
Views
142
Peter_SSs
Peter_SSs
B
  • Question
countifs and remove data completed
2
Replies
10
Views
388
MuchToLearn
M
B
  • Question
Calling macro experts
Replies
4
Views
409
dreid1011
dreid1011
N
  • Solved
Data Extraction With OR Logic Including Dates
Replies
5
Views
219
StephenCrump
S

Forum statistics

Threads
1,215,006
Messages
6,122,666
Members
449,091
Latest member
peppernaut

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top