Search code examples
excelvbacopy-paste

Copy and Paste Cell Values in Excel VBA: Compile and Run Time Errors

I am working in excel VBA to set up a recurring code that will copy formula values out of one worksheet and paste the values into a running list on another worksheet. I can copy and paste the formulas into the desired location, but what do I need to change in this code to copy and paste the values only?

I've tried a variety of variations on this code:

  1. .Value after the copy and/or paste command at the bottom

  2. .PasteSpecial xlPasteValues (which gives the compile error, expected end of statement)

  3. .Value when defining the cells to copy (which gives Run-time error '438')

  4. Changing the Dim type of my copy and paste variables to Variants, Strings, and Range

Sub snapShot()
    Dim sourceSheet As String, destinationSheet As String
    Dim copyR As Range, copyT As Range
    Dim pasteR As Range, pasteT As Range
    sourceSheet = "REPORT"
    destinationSheet = "Historical Changes"

    Set copyR = Worksheets(sourceSheet).Range("I2")
    Set copyT = Worksheets(sourceSheet).Range("I3")

    With Sheets(destinationSheet)
        Set pasteR = .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
        Set pasteT = .Cells(.Rows.Count, 1).End(xlUp)(2, 2)
    End With

    copyR.Copy pasteR.PasteSpecial xlPasteValues 'Compile Error: Expected: End of Statement
    copyT.Copy pasteT.PasteSpecial xlPasteValues 'Compile Error: Expected: End of Statement
    pasteT.Columns(2) = Now() 'This gives a time stamp next the my columns
    Application.OnTime Now() + TimeValue("24:00:00"), "snapShot"

End Sub

If I remove the .PasteSpecial xlPasteValues my code will work properly to copy and paste the formulas, but since I want time stamped data, I only want the values, not the formulas. I am currently getting

Compile Error: Expected: End of Statement

If I change it to .Values I get

Run-time error '438': Object doesn't support this property or method

Please let me know if you see anything that will fix this! I'm sure it's something simple, but I've been stuck for a while on this one.

Solution

  • When using PasteSpecial this bit needs to be on a separate line to the Copy, as in:

    copyR.Copy
pasteR.PasteSpecial xlPasteValues

    In this particular case, one can avoid the clipboard altogether and directly transfer the value, which is shorter in code, and in operation. As in:

    pasteR.value=copyR.value