Find today's date and copy/paste as value adjacent cells

[emoandy05]

Hello,

Starting in cell I12, I have dates listed. In cells J12:AO12 i have data that pulls in based on formulas. I would like a macro that will find today's date in column I, starting in cell I12, and will copy and paste as values the data in the adjacent cells in columns I:AO, starting with J12:AO12.

Thank you!

Emerson

 

[JackDanIce]

Try:

Sub TodayDate()

    Dim LR  As Long
    Dim rng As Range

    Application.ScreenUpdating = False
    
    LR = Cells(Rows.count, 9).End(xlUp).row

    Set rng = Cells(12, 9).Resize(LR - 11).find(what:=Date, LookIn:=xlValues, lookat:=xlWhole).Offset(, 1)
    If Not rng Is Nothing Then rng.Resize(, 32).Value = rng.Resize(, 32).Value
    
    Application.ScreenUpdating = True
        
    Set rng = Nothing
    
End Sub

 

[emoandy05]

Thank you very much. Works well. Would you mind explaining how

If Not rng Is Nothing Then rng.Resize(, 32).Value = rng.Resize(, 32).Value

works?

 

[JackDanIce]

You're welcome.

This line:

Set rng = Cells(12, 9).Resize(LR - 11).find(what:=Date, LookIn:=xlValues, lookat:=xlWhole).Offset(, 1)

Tries to find the date in range I12:I<last row=""> and if it find's it, sets the rng variable to the 1st column to the right using .Offset
Rng initialises as an empty range object.

If Not rng Is Nothing Then rng.Resize(, 32).Value = rng.Resize(, 32).Value

The line above tests if rng is not Nothing. If it's Not nothing, i.e. it has found a matching date (then offset to the right by one column) then resize that cell by 32 columns and make them all equal to the rng's value</last>