Macro to Range Value Data

[howard]

I have formula in Col Q2 onwards. I have written code to copy these from Q2 onwards to the last row containing a formula in Col Q to A2

However the last line of code is highlighted when trying running the macro

Your assistance is most appreciated


Sub RngVal ()
Dim Lr As Long
Lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("Q2:Q" & Lr).PasteSpecial Paste:=xlPasteValues Destination:=Range("A2")
End Sub

 

[Dryver14]

Sub RngVal()
 Dim Lr As Long
 Lr = Cells(Rows.Count, 1).End(xlUp).Row
 Range("Q2:Q" & Lr).Copy
 Range("A2").PasteSpecial xlValues
 Application.CutCopyMode = False
 End Sub

 

[howard]

Thanks for the help, which is most appreciated

 

[Rick Rothstein]

Thanks for the help, which is most appreciated

You should be able to do what you want by directly assigning the values without using copy/paste at all...

Sub RngVal()
  Dim Lr As Long
  Lr = Cells(Rows.Count, 1).End(xlUp).Row
  Range("A2:A" & Lr).Value = Range("Q2:Q" & Lr).Value
End Sub