Vba code to pull content from one sheet to another

[shina67]

Hi All,

I have a couple of reports I download on a daily basis.

One report is the employees name and badge number.
The other report is employees absenteeism.

The problem I have is the following:-

The 'Employee Absences' report does not show the employees full name.
The 'Employee List' does show the employees full name.

I need the sheet 'Employee Absences' to pull in the employees full name from the 'Employee List' sheet.

I then need to create a pivot from this information so I can keep a track of employees absences.
I have attached a copy of the workbook.
The sheets within the workbook are exactly as the reports download. When you look at the reports you will understand more why I an struggling.

I would copy/paste the downloaded reports directly into the 'Employee Absences' and 'Employee List' sheets daily. Hopefully then run some code to give me the above.

Any help would be greatly appreciated.
The below are in one workbook.

Employee List & Absences.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
1
2		Employee Absences
3		Wed 21 September 2022
4
5
6
7		Badge		Name				Company				Department							Group					Date					Absence Code							Time Taken
8
9
10
11		00006		GEOFFREY				Express Bi folding Doors				Production							Fabrication					Mon		07/03/2022			SIKUP							0:00
12
13																								Tue		08/03/2022			SIKUP							0:00
14
15
16																								Wed		09/03/2022			SIKUP							0:00
17
18																								Thu		10/03/2022			SIKUP							0:00
19
20
21
22
23		Absences with deduct value equal to 0 :											0							Absences with deduct value equal to 1/4 day :														0
24
25		Absences with deduct value equal to 1/2 day :											0							Absences with deduct value equal to a full day :														4
26
27		Absences with deduct value equal to 3/4 day :											0
28
29
30
31		Total absence records :						4		Total number of unauthorised absences :												0					Total time in absence :									0:00
32
33
34
35
36
37		Badge		Name				Company				Department							Group					Date					Absence Code							Time Taken
38
39
40
41		00113		Massingham				Express Bi folding Doors				Production							Machining					Fri		01/04/2022			SIKUP							0:00
42
43																								Wed		13/07/2022			SIKUP							0:00
44
45
46																								Thu		14/07/2022			SIKUP							0:00
47
48																								Fri		15/07/2022			SIKUP							0:00
Employee Absences

Employee List & Absences.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB
1
2	Employee List & Payroll Number
3	Wed 21 September 2022
4
5				Badge			Name																																																																	Payroll No.
6
7				00006			GEOFFREY NEIL GREENWOOD																																																																			6
8
9
10
11				00042			ROBERT MOUNTAIN																																																																																											42
12
13
14
15				00113			JOHN DAVID MASSINGHAM																																																																												113
16
Employee List

 

[OaklandJim]

I tried to give you what was requested. Code loops through column E looking for "short" names. If found, the sub uses Function FindStringInSheet to find the full name in the other worksheet. That full name is put into the cell with the short name.

Function FindStringInSheet(pwsSheet As Worksheet, psFindString As String, Optional ByRef prCell)
    
    Dim rCellFound As Range
    
    Set rCellFound = Nothing
    
    FindStringInSheet = ""
    
'   Find may look for "part" (xlPart) or whole (xlWhole)
    On Error Resume Next
    Set rCellFound = pwsSheet.Cells.Find( _
        What:=psFindString, LookAt:=xlPart, SearchOrder:=xlByRows)
    On Error GoTo 0
    
    If Not rCellFound Is Nothing _
     Then
        If Not IsMissing(prCell) Then Set prCell = rCellFound
        FindStringInSheet = rCellFound.Address
    End If
    
End Function

Sub GetFullNames()

'   Last occupied row in the column containing names.
    Dim iLastRow As Long
    
'   Column # in which short names are located.
    Dim iColNumber As Long
    
'   Range to search looking for short names.
    Dim rSearchRange As Range

'   Used to loop through rSearchRange
    Dim rCell As Range
    
'   Short name found.
    Dim sShortName As String
    
'   Cell containing long name if found.
    Dim rFoundCell As Range
    
'   Workbook where full names are needed
    Dim wsTarget As Worksheet
    
'   Workbook where full names are located
    Dim wsSource As Worksheet
    
    Set wsTarget = ThisWorkbook.Worksheets("Sheet1")
    Set wsSource = ThisWorkbook.Worksheets("Sheet2")
    
'   Assume name data is in column E (i.e., 5). Otherwise reset this value.
    iColNumber = 5
    
    With wsTarget
    
        iLastRow = .Cells(1, iColNumber).Offset(100000).End(xlUp).Row
    
        Set rSearchRange = .Range(.Cells(1, iColNumber), .Cells(iLastRow, iColNumber))
    
        For Each rCell In rSearchRange
        
            If rCell.Value <> "" And UCase(rCell.Value) <> "NAME" _
             Then
                sShortName = UCase(rCell.Value)
                
                Call FindStringInSheet(wsSource, sShortName, rFoundCell)
                
                If Not rFoundCell Is Nothing _
                 Then
                    rCell.Value = rFoundCell.Value
                End If
            
            End If
        
        Next rCell
    
    End With 'wsTarget

End Sub