Apologies for the long code below in advance. The code simply looks at 1 cell in sheet 2 that is a date to determine what to do with the cells in sheet 1. The code that I have below works, but it takes about 20 seconds to run it which seems like more than it should, and I feel like the code could be a lot shorter (if only I was better with coding frowns ) I had to find a similar code online that would work, I figure it’s not the best one to use in this situation.

End goal here is to take cell C:24 from sheet2 and find this date in Row 4 of sheet1. Then in sheet 1 I am just copying and pasting cells as values in their specific rows in that same column. The rows are listed below (I only need the cells in those rows for the specific column pasted as a value)

Example: Sheet2 cell C:24 = 9/2/2021 In sheet1 “9/2/2021” is in column ABC:4 According to the code below I would want ABC:18 , ABC:19 (and so on) copied and pasted as a value in the same cell that they are currently in (they are currently formulas in their respective cells)

```
Public Sub Paste_Amounts_As_Values()
Dim todayDate, tomorrowDate As Date
Dim sourceID, targetID As Integer
Dim countdate As Range
Dim wS As Worksheet
Dim aRowVal, bRowVal, cRowVal, dRowVal, eRowVal, fRowVal, gRowVal, hRowVal, jRowVal, kRowVal,
lRowVal, mRowVal, nRowVal, oRowVal, pRowVal, qRowVal, rRowVal, sRowVal As String
Worksheets("Sheet2").Activate
todayDate = Sheets("Sheet2").Range("C24").Value
Worksheets("Sheet1").Activate
Set wS = ThisWorkbook.Worksheets("Sheet1")
lastcol = wS.Cells(4, 4).End(xlToRight).Column
'dateRow = wS.Range("C24").Cells(4, lastcol).Value
ReDim selectData(1 To lastcol) As Variant
For i = 1 To lastcol - 1
selectData(i) = wS.Cells(4, i + 1)
Next i
For i = 1 To lastcol - 1
If selectData(i) = todayDate Then 'Cells to Copy
aRowVal = ActiveSheet.Cells(18).Formula
bRowVal = ActiveSheet.Cells(19).Formula
cRowVal = ActiveSheet.Cells(29).Formula
dRowVal = ActiveSheet.Cells(30).Formula
eRowVal = ActiveSheet.Cells(40).Formula
fRowVal = ActiveSheet.Cells(41).Formula
gRowVal = ActiveSheet.Cells(51).Formula
hRowVal = ActiveSheet.Cells(52).Formula
jRowVal = ActiveSheet.Cells(62).Formula
kRowVal = ActiveSheet.Cells(63).Formula
lRowVal = ActiveSheet.Cells(73).Formula
mRowVal = ActiveSheet.Cells(84).Formula
nRowVal = ActiveSheet.Cells(94).Formula
oRowVal = ActiveSheet.Cells(105).Formula
pRowVal = ActiveSheet.Cells(115).Formula
qRowVal = ActiveSheet.Cells(116).Formula
rRowVal = ActiveSheet.Cells(126).Formula
sRowVal = ActiveSheet.Cells(179).Formula
sourceID = i + 1
'Debug.Print aRowVal
'Debug.Print bRowVal
End If
Next i
If sourceID = 0 Then
MsgBox ("There is no match date with Today")
Else
For i = 1 To lastcol - 1
If selectData(i) = todayDate Then 'Pasting as Value
ActiveSheet.Cells(18) = aRowVal
ActiveSheet.Cells(18, sourceID) = ActiveSheet.Cells(18, sourceID)
ActiveSheet.Cells(19) = bRowVal
ActiveSheet.Cells(19, sourceID) = ActiveSheet.Cells(19, sourceID)
ActiveSheet.Cells(29) = cRowVal
ActiveSheet.Cells(29, sourceID) = ActiveSheet.Cells(29, sourceID)
ActiveSheet.Cells(30) = dRowVal
ActiveSheet.Cells(30, sourceID) = ActiveSheet.Cells(30, sourceID)
ActiveSheet.Cells(40) = eRowVal
ActiveSheet.Cells(40, sourceID) = ActiveSheet.Cells(40, sourceID)
ActiveSheet.Cells(41) = fRowVal
ActiveSheet.Cells(41, sourceID) = ActiveSheet.Cells(41, sourceID)
ActiveSheet.Cells(51) = gRowVal
ActiveSheet.Cells(51, sourceID) = ActiveSheet.Cells(51, sourceID)
ActiveSheet.Cells(52) = hRowVal
ActiveSheet.Cells(52, sourceID) = ActiveSheet.Cells(52, sourceID)
ActiveSheet.Cells(62) = jRowVal
ActiveSheet.Cells(62, sourceID) = ActiveSheet.Cells(62, sourceID)
ActiveSheet.Cells(63) = kRowVal
ActiveSheet.Cells(63, sourceID) = ActiveSheet.Cells(63, sourceID)
ActiveSheet.Cells(73) = lRowVal
ActiveSheet.Cells(73, sourceID) = ActiveSheet.Cells(73, sourceID)
ActiveSheet.Cells(84) = mRowVal
ActiveSheet.Cells(84, sourceID) = ActiveSheet.Cells(84, sourceID)
ActiveSheet.Cells(94) = nRowVal
ActiveSheet.Cells(94, sourceID) = ActiveSheet.Cells(94, sourceID)
ActiveSheet.Cells(105) = oRowVal
ActiveSheet.Cells(105, sourceID) = ActiveSheet.Cells(105, sourceID)
ActiveSheet.Cells(115) = pRowVal
ActiveSheet.Cells(115, sourceID) = ActiveSheet.Cells(115, sourceID)
ActiveSheet.Cells(116) = qRowVal
ActiveSheet.Cells(116, sourceID) = ActiveSheet.Cells(116, sourceID)
ActiveSheet.Cells(126) = rRowVal
ActiveSheet.Cells(126, sourceID) = ActiveSheet.Cells(126, sourceID)
ActiveSheet.Cells(179) = sRowVal
ActiveSheet.Cells(179, sourceID) = ActiveSheet.Cells(179, sourceID)
targetID = i + 1
'Debug.Print ActiveSheet.Cells(9, i + 1)
'Debug.Print ActiveSheet.Cells(11, i)
End If
Next i
If targetID = 0 Then
MsgBox ("There is no match date with Tomorrow")
End If
End If
End Sub
```