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

ExcelNewbie2020

Active Member
Joined
Dec 3, 2020
Messages
350
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUV
1SHEET 1SHEET 2
2
3NAMEEMP.#22-02-2323-02-2324-02-2325-02-2326-02-2327-02-2328-02-2301-03-2302-03-23EMP.#DATE24-02-2325-02-2326-02-2327-02-2328-02-2301-03-2302-03-23
4NAME1101FFFFFFFFF10224-02-23T888888
5NAME2102VVT88888810724-02-23T888888
6NAME3103FFFFFFFFF11124-02-23T888888
7NAME410488888888811424-02-23T888888
8NAME5105888888888
9NAME6106888888888
10NAME7107FFT888888
11NAME8108FFFFFFFFF
12NAME9109FFFFFFFFF
13NAME10110FFFFFFFFF
14NAME11111SST888888
15NAME12112VVVVVVVVV
16NAME13113EEEEEEEEE
17NAME14114FFT888888
18NAME15115888888888
19
20
21
22
Sheet28
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Maybe something like:
VBA Code:
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
 
Upvote 0
Maybe something like:
VBA Code:
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..
1677232141085.png


1677232205924.png
 
Upvote 0
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?
 
Upvote 0
What is it that the date in column B of sheet2 does, does it produce the dates in row 1 of sheet2?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

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