Copy and paste fail vba

Question

I am trying to copy a range and paste it as values using vba but it seems like it steps through the codes but didnt do anything since I still have formulas after I ran it.

Column R and S are the only places that have formulas and I tried F8 and it steps through everything just didn't do its work? Maybe I got the wrong codes for pasting as values but here they are. All the columns have the same # of rows. So there is no error just didn't copy and paste as values.

If someone can recommend a more efficient way to copy and paste a range as values only, please share as well.

Sub test()
 Dim ws2 As Worksheet
 Dim LR3 As Long
Set ws2 = Worksheets("BRST")
LR3 = ws2.Cells(ws2.Rows.Count, "R").End(xlUp).Row
ws2.Range("R3", "S" & LR3).Copy
ws2.Range("R3", "S" & LR3).PasteSpecial xlPasteValues
End Sub

Show source
| excel-vba   | vba   | excel   2017-01-05 18:01 2 Answers

Answers ( 2 )

  1. 2017-01-05 18:01

    You should avoid using Copy and Paste in VBA as it is a lot slower than just communicating with the cells themselves, if you want to simply replace the formulas in cells running from R3 to the last row in column S then use this instead:

    Sub test()
    
    Dim ws2 As Worksheet
    Dim LR3 As Long
    
    Set ws2 = Worksheets("BRST")
    LR3 = ws2.Cells(ws2.Rows.Count, "R").End(xlUp).Row
    
    ws2.Range("R3", "S" & LR3).Value = ws2.Range("R3", "S" & LR3).Value
    
    End Sub
    
  2. 2017-01-05 18:01

    If you just want to convert a range to values:

    With ws2.Range("R3", "S" & LR3)
        .Value = .Value
    End With
    
◀ Go back