Search code examples
excelvbadatecopy-paste

VBA Macro to copy/paste cell value from sheet 1 to 2 based on date

I have anchored a date and a value in Sheet 1 and want to look up that date in Sheet 2's column A, and paste the sheet 1 value in sheet 2's column E.

In sheet 1, I have today's name as REP_DATE, and the date I want to look for is yesterday's date, which is REP_DATE - 1 and I have had this formula in sheet1 E5, and the value is in F5.

here is the code I have from online and I have made some adjustments:

Sub CopyData()
Dim myDate As Date
Dim myValue As Variant
myDate = ThisWorkbook.Sheets("Sheet1").Range("E6").Value
myValue = ThisWorkbook.Sheets("Sheet1").Range("F6").Value


ThisWorkbook.Sheets("Sheet2").Range("A:A").Find(myDate).Offset(0, 4).Value = myValue

End Sub

However, I'm keep getting an error "Run-time error '91': Object variable or With block variable not set", debug is referring to this line of codeThisWorkbook.Sheets("Sheet2").Range("A:A").Find(myDate).Offset(0, 4).Value = myValue

It really confuses me and I'm wondering how to solve this issue.

Thanks.

Solution

  • Thanks to @Salamander Krajza, I will share my most updated code here, need to change the date on sheet2 from formula to value so that find function can work its magic:

    Sub CopyData()
Dim myDate As Date
Dim myValue As Variant
myDate = ThisWorkbook.Sheets("Sheet1").Range("E5").Value
myValue = ThisWorkbook.Sheets("Sheet1").Range("F5").Value


On Error GoTo nodate
ThisWorkbook.Sheets("Sheet2").Range("A:A").Find(what:=myDate, LookIn:=xlValues, lookat:=xlWhole).Offset(0, 4) = myValue
On Error GoTo 0

Exit Sub

nodate:
    MsgBox ("Date is not found")
End Sub