Copy and paste values and not formulas

Question

Writing macro for the first time, I have to copy only cell values to another and which I got it working, however, I am not sure, how to copy entire column without specifying range since range may be different every time. Here, I am trying with a range which is working, but I want it to check values of cell for that column and until it finds a value copy/paste to the another column.

Here is the code I have so far:

Sub CopyingCellValues()

Range("E2:E7").Copy
Range("C2:C7").PasteSpecial xlPasteValues

End Sub

Thanks.

0m3r · Accepted Answer · 2016-02-17 23:02:12Z

3

Simple Columns copy will be...

Sheets("Sheet Name").Columns(1).Copy Destination:=Sheets("Sheet Name").Columns(2)

Helpful info at MSDN on Getting Started with VBA in Excel 2010

Edit:

With out the formula, Try

Sub CopyingCellValues()
    Range("E:E").Value = _
    Range("C:C").Value
End Sub

Sub ValueToValue()
    [E:E].Value = [C:C].Value
End Sub

edited Feb 17, 2016 at 23:02

answered Jan 10, 2016 at 6:01

0m3r

12.5k15 gold badges40 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Udaan Over a year ago

I did paste special since the column I am copying from is being derived from a formula, but when I only need to copy and paste value and not formula. Do I need to do Sheet Names even though its in the same sheet?

Udaan Over a year ago

Thanks for the help!

Collectives™ on Stack Overflow

Copy and paste values and not formulas

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related