VBA: loop funtion to find data based on cell value and transpose a range then continue

[xslugx]

I have a range of data emp number, last name, first name, total minutes, coach and date, there is up to 100 rows as we pull this information daily.
I need to be able to compare the date to today's date then copy the first and last name of each employee when the date column equals today's date on a worksheet named for each month in today's date cell (this is formatted as a calendar).

I have always had issues with any loops, I could do a nested if statement however that's messy and long and should be a lot easier. so maybe a do while to step through each cell in the date column, maybe I'm asking the wrong question, this could also be why I cannot find the solution.

This is what I have so far, I know its not a loop but its a start

Sub emp_days()
    'This macro updates the calandar with the information required by matching dates.
        'turns off updating
    Application.ScreenUpdating = False
        'Set variable to cell/s to reference for data require
    Dim Today As String
        Today = Sheets("sheet1").Range("O2").Value
    
    If Sheets("Sheet1").Range("$F$5").Value = Today Then
        Sheets("Sheet1").Activate
        Range("C5:B5").Select
        Selection.Copy
        Range("G2").Select
        Selection.PasteSpecial
                
    End If
    
End Sub

 

[xslugx]

Bump

 

[NoSparks]

Best not to use reserved words as variable names.
I know Today is in Excel and not in VBA, but just the same, best to avoid any confusion.

If you're going to use the value from o2 as a string I think you might need to coerce the dates of column F to strings by using Cstr

Does this work ?

Sub emp_days()
    'This macro updates the calandar with the information required by matching dates.
        'turns off updating
    Application.ScreenUpdating = False
        'Set variable to cell/s to reference for data require
    
    Dim sToday As String
    Dim rng As Range
    Dim cl As Range
    
    With Sheets("Sheet1")
        sToday = .Range("o2").Value
        Set rng = .Range("F5", .Range("F" & Rows.Count).End(xlUp))
        For Each cl In rng
            If cl.Value = sToday Then
                cl.Offset(, 1).Value = cl.Offset(, -3).Value & " " & cl.Offset(, -4).Value
            End If
        Next cl
    End With
    
    Application.ScreenUpdating = True
    
End Sub

 

[xslugx]

This "almost" works, it does exactly what I'm looking for but it only gave me 1 of the selections as I had set up a test with data in column b,c and f. I set 2 cells in F with today's date and the rest with different dates and it only gave me the last one in the list not all of them.

Best not to use reserved words as variable names.
I know Today is in Excel and not in VBA, but just the same, best to avoid any confusion.

If you're going to use the value from o2 as a string I think you might need to coerce the dates of column F to strings by using Cstr

Does this work ?

Sub emp_days()
    'This macro updates the calandar with the information required by matching dates.
        'turns off updating
    Application.ScreenUpdating = False
        'Set variable to cell/s to reference for data require
    
    Dim sToday As String
    Dim rng As Range
    Dim cl As Range
    
    With Sheets("Sheet1")
        sToday = .Range("o2").Value
        Set rng = .Range("F5", .Range("F" & Rows.Count).End(xlUp))
        For Each cl In rng
            If cl.Value = sToday Then
                cl.Offset(, 1).Value = cl.Offset(, -3).Value & " " & cl.Offset(, -4).Value
            End If
        Next cl
    End With
    
    Application.ScreenUpdating = True
    
End Sub

 

[xslugx]

I fixed it, I just realized it starts at F5 not F2 lol