Unique values from multiple sheets

[tezza]

Hi All

I've got a bit of a project going on here and may need a bit of help if possible please?

First bit, I would like to list all of the staff within the company based on multiple sheets of data as staff come and go so not all sheets are the exactsame.

Sheets labels
WK 1, WK 2, ...... WK 51, WK 52 (so week 1 to 52)

Cell A2 down lists staff employee numbers
Col B2 down lists staff names
Col C2 down shows total hours they worked that week

1) How would I go about creating a summary sheet that grabs both the unique staff number, name (together) and hours from all sheets then list everyone into the summary sheet once per emp num and name

2) Count how many times the above has appeared over the 52 weeks so I can work out pro-rata later on.

My list will then show each staff emp no, staff name, Weeks staff appears, total hours worked over 52 weeks, average hours worked (total hours/times appeared)

EG: (minus the ____ as they're just for spacing purpose here)

No:___Name__Appears___Sum of hours___Avg weekly hours
1234_Tezza______20___________500____________=500/20
Next unique staff.....

There are breaks between some of the sheets unfortunately so it'll have to search name specific but having numbers that increment will hopefully make it easier?

See what you think at least

Kind regards
Tezza

 

[mumps]

Replace this line of code:

desWS.UsedRange.Offset(1).ClearContents

with this line:

desWS.Range("A2", desWS.Range("G" & Rows.Count).End(xlUp)).ClearContents