Excel vba to transfer value to another sheet based on date and number

[ExcelNewbie2020]

i have a task to update the attendance daily (sheet 1). i was thinking of preparing a summary on sheet 2 then will auto-update it to sheet 1 with a button.

would it be possible to transfer the value from sheet 2 to sheet 1 based on the date and emp. #?. while those not included in the update will just carry over based on previous value..

testing.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	SHEET 1											SHEET 2
2
3	NAME	EMP.#	22-02-23	23-02-23	24-02-23	25-02-23	26-02-23	27-02-23	28-02-23	01-03-23	02-03-23	EMP.#	DATE	24-02-23	25-02-23	26-02-23	27-02-23	28-02-23	01-03-23	02-03-23
4	NAME1	101	F	F	F	F	F	F	F	F	F	102	24-02-23	T	8	8	8	8	8	8
5	NAME2	102	V	V	T	8	8	8	8	8	8	107	24-02-23	T	8	8	8	8	8	8
6	NAME3	103	F	F	F	F	F	F	F	F	F	111	24-02-23	T	8	8	8	8	8	8
7	NAME4	104	8	8	8	8	8	8	8	8	8	114	24-02-23	T	8	8	8	8	8	8
8	NAME5	105	8	8	8	8	8	8	8	8	8
9	NAME6	106	8	8	8	8	8	8	8	8	8
10	NAME7	107	F	F	T	8	8	8	8	8	8
11	NAME8	108	F	F	F	F	F	F	F	F	F
12	NAME9	109	F	F	F	F	F	F	F	F	F
13	NAME10	110	F	F	F	F	F	F	F	F	F
14	NAME11	111	S	S	T	8	8	8	8	8	8
15	NAME12	112	V	V	V	V	V	V	V	V	V
16	NAME13	113	E	E	E	E	E	E	E	E	E
17	NAME14	114	F	F	T	8	8	8	8	8	8
18	NAME15	115	8	8	8	8	8	8	8	8	8
19
20
21
22
Sheet28

 

[Georgiboy]

Maybe something like:

Sub test()
    Dim fRng As Range, fVar As Variant, f As Long
    Dim tRng As Range, tVar As Variant, t As Long
    Dim x As Long
    
    Set fRng = Sheet2.UsedRange
    With fRng
        Set fRng = .Offset(1).Resize(.Rows.Count - 1)
    End With
    fVar = fRng.Value
    
    Set tRng = Sheet1.UsedRange
    With tRng
        Set tRng = .Offset(1).Resize(.Rows.Count - 1)
    End With
    tVar = tRng.Value
    
    For t = 1 To UBound(tVar)
        For f = 1 To UBound(fVar)
            If tVar(t, 2) = fVar(f, 1) Then
                For x = 5 To 11
                    tVar(t, x) = fVar(f, x - 2)
                Next x
            End If
        Next f
    Next t
    tRng = tVar
End Sub

 

[ExcelNewbie2020]

Maybe something like:

Sub test()
    Dim fRng As Range, fVar As Variant, f As Long
    Dim tRng As Range, tVar As Variant, t As Long
    Dim x As Long
   
    Set fRng = Sheet2.UsedRange
    With fRng
        Set fRng = .Offset(1).Resize(.Rows.Count - 1)
    End With
    fVar = fRng.Value
   
    Set tRng = Sheet1.UsedRange
    With tRng
        Set tRng = .Offset(1).Resize(.Rows.Count - 1)
    End With
    tVar = tRng.Value
   
    For t = 1 To UBound(tVar)
        For f = 1 To UBound(fVar)
            If tVar(t, 2) = fVar(f, 1) Then
                For x = 5 To 11
                    tVar(t, x) = fVar(f, x - 2)
                Next x
            End If
        Next f
    Next t
    tRng = tVar
End Sub

thanks man, i follow the code.. it did transfer the values from sheet 2 to sheet 1, but it didn't fill the values for those not included in sheet 2. Also, if I update the date in sheet 2 (column B say 25/2/2023).. will it also move to same date (column F) of sheet 1? Please see photo of sheet 1 and 2..

 

[Georgiboy]

I overlooked the date part, will have another look in a while unless there is another solution offered.

With the sheet1 piece, on the demo data there were already values in the cells where there is no matching EMP# on sheet2. I thought you would need those values left as is and only update the EMP's that are on sheet2?

 

[Georgiboy]

What is it that the date in column B of sheet2 does, does it produce the dates in row 1 of sheet2?

 

[ExcelNewbie2020]

What is it that the date in column B of sheet2 does, does it produce the dates in row 1 of sheet2?

thank you, Basically, that date (column B sheet 2) is the date of travel for the employee. it should match the date on sheet 1.. we can disregard the dates in row 1 sheet 2.

 

[ExcelNewbie2020]

I overlooked the date part, will have another look in a while unless there is another solution offered.

With the sheet1 piece, on the demo data there were already values in the cells where there is no matching EMP# on sheet2. I thought you would need those values left as is and only update the EMP's that are on sheet2?

actually that's the original plan, but if its possible to populate values from those not included in sheet 2, (I also do it manually) why not?..it can make my life easier

 

[Georgiboy]

So you want to update the 24/02 > 02/03 in sheet 1 with the data from sheet2 by EMP#?

If you have the dates in row 1 of sheet2 then what is the need for the date in column B of sheet2 or is it the case that sheet2 can be larger than the supplied data and column B in sheet2 is the start date to move 7 days of data over to sheet1?

actually that's the original plan, but if its possible to populate values from those not included in sheet 2, (I also do it manually) why not?..it can make my life easier

What values would it populate them with if they are not already on sheet1?

 

[ExcelNewbie2020]

So you want to update the 24/02 > 02/03 in sheet 1 with the data from sheet2 by EMP#?

yes.. emp# and date as reference.

If you have the dates in row 1 of sheet2 then what is the need for the date in column B of sheet2 or is it the case that sheet2 can be larger than the supplied data and column B in sheet2 is the start date to move 7 days of data over to sheet1?

row1 of sheet2 (date) is not nedeed..i only show it on my table as reference when transfer to sheet 1.. yes, column B is the start date and move up to 7 days. (column B (dates) is dynamic).

What values would it populate them with if they are not already on sheet1?

the last value in sheet 1..let say, I will update 24/2/2023..whatever value is in 23/2/2023 it should be the same value up to 7days.

 

[Georgiboy]

Will the date be the same for each EMP# in sheet2 for each time the macro is run or could EMP102 be 24/02/2023 & EMP107 bet 26/02/2023?