Error on Copy and Paste MACRO - HELP

[robersha]

I am trying to copy and paste the value of a certain cell (D16) onto another sheet. This value changes every hour. I have the below code but I am getting an error message at frng.Offset(1,0).PasteSpecial (xlPasteValues). Its telling me Run-time error '91': Object variable or With Block variable not set.

Any idea whats causing this issue? Recommend any changes?

Sub CopyPaste2()
    Dim ws1 As Worksheet, ws2 As Worksheet, rng As Range, frng As Range

    Set ws1 = Worksheets("Sheet2")
    Set ws2 = Worksheets("Sheet3")
    Set rng = ws1.Range("D16")
    Set frng = ws2.Rows(1).Find(What:=Time, After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
             , SearchFormat:=False)

    rng.Copy
    frng.Offset(1, 0).PasteSpecial (xlPasteValues)
    Application.CutCopyMode = 0

End Sub

 

[C Moore]

after rng.Copy, put ws2.activate. See if that solves it.

 

[robersha]

Thanks C Moore.

I did try that and received an error. I noticed that it is finding the system time. What i need it to do is match cell A2 on Sheet2 to the same time in ROw 1 on Sheet 3. then paste the data from D16 under that matched time frame.

Would you be able to help out on this? I have been stuck for over an hour.

Much Appreciated!

 

[robersha]

So I have updated the code to reference the proper cells. I still though am receiving an error message at the same part as before. I tried your suggestion of putting ws2.activate and it did not resolve the issue.

Sub CopyPaste()
    Dim ws1 As Worksheet, ws2 As Worksheet, rng As Range, frng As Range

    Set ws1 = Worksheets("Sheet2")
    Set ws2 = Worksheets("Sheet3")
    Set rng = ws1.Range("D16")
    Set frng = ws2.Rows(1).Find(What:=Range("Sheet2!A2"), After:=Range("Sheet3!A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
             , SearchFormat:=False)

    rng.Copy
    frng.PasteSpecial (xlPasteValues)
    Application.CutCopyMode = 0

End Sub

I am soooo close I can feel it. Any suggestions anyone?

 

[robersha]

After playing around some and asking another site, I was able to accomplish what I needed to do. Here is the final code:

Sub CopyPaste2()
    Dim ws1 As Worksheet, ws2 As Worksheet, rng As Range, frng As Range

    Set ws1 = Worksheets("Sheet2")
    Set ws2 = Worksheets("Sheet3")
    Set rng = ws1.Range("D16")
    Set frng = ws2.Rows(1).Find(What:=ws1.Range("A2"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
             , SearchFormat:=False)

    rng.Copy
    frng.Offset(1, 0).PasteSpecial (xlPasteValues)
    Application.CutCopyMode = 0

End Sub

Thanks everyone!