Transferring names from daily timesheet to weekly overview sheet


New Member
Mar 24, 2021
Office Version
  1. 365
  1. Windows
I need to make a module which would transfer data (Name, Trade and Working Duration) from a week's operatives sign-in/sign-out sheet (Daily Sheet) into a weekly overview sheet (Weekly Sheet).

Daily Sheet has each login throughout a week as a separate case. This means that each unique name can repeat in up to 7 separate rows in the Daily Sheet - e. g. John Doe worked for 9 hours on March 22nd, 23rd, 24th and 25th = 4 separate sign in/sign out rows in the Daily Sheet created.

When transferred to the Weekly Sheet, 'John Doe' should have only 1 designated row. In the same row, next to the name, there are 7 cells for the John Doe's working duration of each day of the week. The working durations are already provided in the Daily Sheet within a particular operative's sign-in/sign-out case row.

Sample of the sheets that I'm talking about:

Exact things that should be automated:

1) Create full names from First+Last name columns in the Daily Sheet and transfer all the unique ones to the Weekly Sheet:

Daily Sheet First Name (merged columns C and D) + Last Name (column E) ---> Weekly Sheet NAME (merged columns C, D and E)
2) Extract trade of each unique name found on Daily Sheet and transfer to the according name in the Weekly Sheet (found on the same row as the name):

Daily Sheet Name's Trade (column L) ---> Weekly Sheet Name's TRADE (column B)
3) From the date provided in a sign-in/sign-out case row, define which day of the week the date is (WEEKDAY function?). Depending on which day it is, choose one of the 7 cells next to a name in the Weekly Sheet and put that day's working duration of an operative. So if we use WEEKDAY and 1 is Sunday and 7 is Saturday:

Daily Sheet If date in column N = 7 (Saturday), then working duration in Daily Sheet's column U cell ------> Weekly Sheet column F cell.
N = 1 (Sunday) --> Weekly Sheet column G cell
N = 2 (Monday) --> Weekly Sheet column H cell
N = 3 (Tuesday) --> Weekly Sheet column I cell
N = 4 (Wednesday) --> Weekly Sheet column J cell
N = 5 (Thursday) --> Weekly Sheet column K cell
N = 6 (Friday) --> Weekly Sheet column L cell
The dates use the following custom format: [$-en-GB,1]dd/mm/yyyy
NOTE: relevant data in the Daily Sheet starts on ROW 9 and the table cells where info should be transferred in the Weekly Sheet starts on ROW 21.

4) BONUS: Arrange Weekly Sheet's data alphabetically by the TRADES first and then by NAMES secondly (so it should prioritize alphabetical arrangement by TRADES and then operatives within a same TRADE should be arranged in alphabetical order again without breaking the TRADES one). When rearranging, the operative should move as an entire row (not leaving his working duration behind).

I already have a VBA module from one forum. It should do the first 2 things (transfer unique names and trade for each name), but it unnecessarily adds empty lines in-between the names, starts the list at a wrong row, doesn't paste into merged cells of weekly sheet and does nothing about the dates/working durations. I tried tinkering with it and it may need a review from a competent person, but it may serve a start for someone helping:

Sub names()
Set wbk = ThisWorkbook
Set sh1 = wbk.Sheets("Daily Sheet")
Set sh2 = wbk.Sheets("Weekly Sheet")
lastrow = sh1.Range("D100000").End(xlUp).Row
For i = 9 To lastrow
weekly_lastrow = sh2.Range("D100000").End(xlUp).Row

full_name = Join(Array(sh1.Range("C" & i), sh1.Range("D" & i)), " ")

If Not sh2.Range("D2:D" & weekly_lastrow).Find(full_name) Is Nothing Then >GoTo next_name

sh2.Range("D" & weekly_lastrow + 1).Value = full_name

sh2.Range("B" & weekly_lastrow + 1).Value = sh1.Range("L" & i).Value

sh2.Range("D" & weekly_lastrow).Offset(1).EntireRow.Insert Shift:=xlDown

Next i
End Sub
Thanks in advance for anyone that will be kind enough to help!

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Forum statistics

Latest member

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
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 "".
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