Easy - paste values code

[Patrunjel]

Hi,

I am trying to copy only the values (NOT the formulas) from one location to another. My current code is:

With Sheets("Main Page")
    .Range("G12", .Range("G" & Rows.Count).End(xlUp)).Copy _
    Sheets("Comparison").Range("B" & Rows.Count).End(xlUp)(2)
    End With

Obviously, this only copies formulas. Could anyone be so kind to tell me how to change this to copy values only?

Thank you.

 

[circledchicken]

Hi,

Perhaps try:

Sub example()

With Sheets("Main Page").Range("G12", .Range("G" & Rows.Count).End(xlUp))
    Sheets("Comparison").Range("B" & Rows.Count).End(xlUp)(2).Resize(.Rows.Count, 1).Value = .Value
End With

End Sub

 

[Patrunjel]

Thank you, but it returns an error:

“Compile error: Invalid or unqualified reference”</SPAN>

It highlights the word .Range after “G12”,</SPAN>

 

[grd]

Try using this

Range("named range").Copy
Range("location").PasteSpecial Paste:=xlPasteValues

 

[circledchicken]

Thank you, but it returns an error:

“Compile error: Invalid or unqualified reference”

It highlights the word .Range after “G12”,

Oops - I made a mistake. Perhaps try grd's alternative or try it this way:

Sub example()

With Sheets("Main Page").Range("G12", Sheets("Main Page").Range("G" & Rows.Count).End(xlUp))
    Sheets("Comparison").Range("B" & Rows.Count).End(xlUp)(2).Resize(.Rows.Count, 1).Value = .Value
End With


End Sub

 

[Patrunjel]

Oops - I made a mistake. Perhaps try grd's alternative or try it this way:

Sub example()

With Sheets("Main Page").Range("G12", Sheets("Main Page").Range("G" & Rows.Count).End(xlUp))
    Sheets("Comparison").Range("B" & Rows.Count).End(xlUp)(2).Resize(.Rows.Count, 1).Value = .Value
End With


End Sub

Thank you for the fix it worked great! Thanks a lot!

 

[circledchicken]

Thank you for the fix it worked great! Thanks a lot!

Your welcome!