Easy - paste values code

Patrunjel

New Member
Joined
Feb 10, 2012
Messages
9
Hi,

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

Code:
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.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Hi,

Perhaps try:

Code:
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
 
Upvote 0
Thank you, but it returns an error:

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

It highlights the word .Range after “G12”,</SPAN>
 
Upvote 0
Try using this

Range("named range").Copy
Range("location").PasteSpecial Paste:=xlPasteValues
 
Upvote 0
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:

Code:
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
 
Upvote 0
Oops - I made a mistake. Perhaps try grd's alternative or try it this way:

Code:
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!
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top