Okay, this looks for today's date in column A and then pastes the formula in that row converting them to values.
I'm guessing in your original file you did not mean to keep all those formulas in B2:F23
as they would constantly change. So again, this code will copy the formula to the row with today's date and then convert to values.
Code:
Sub Rearrange()
Dim LR As Long
Dim Found As Range
Dim MyFound As Long
Const sFormula As String = "=COUNTIF(Status!$A$2:$A$#, B$1)"
Const sFormula1 As String = "=COUNTIF(Status!$B$2:$B$#, D$1)"
Set Found = Columns(1).Find(What:=Format(Date, "d-mmm-yy"), LookIn:=xlValues)
MyFound = Found.Row - 1
LR = Sheets("Status").Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
With Sheets("Data").Range("B" & Found.Row).Resize(, 2)
.Formula = Replace(sFormula, "#", LR)
.Value = .Value
End With
With Sheets("Data").Range("D" & Found.Row).Resize(, 3)
.Formula = Replace(sFormula1, "#", LR)
.Value = .Value
End With
Application.ScreenUpdating = True
End Sub