Copy and paste values and not formulas

3
votes

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.

1
vbaexcel

1 Answers

3
votes

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