Copy and paste fail vba

4
votes

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
3
vbaexcel

3 Answers

4
votes

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
1
votes

If you just want to convert a range to values:

With ws2.Range("R3", "S" & LR3)
    .Value = .Value
End With
0
votes

Try this

Dim i As Long
Application.ScreenUpdating = False 

i = Range("A" & Rows.Count).End(xlUp).Row
Range("N12:W" & i).FillDown

Application.ScreenUpdating = True