Paste Special in macro not working

[scrupul0us]

Ive got an entire column of formulated text in a column, and im trying to copy just the result over to another column, when I manually do paste special > VALUES it works fine. When it embedded in my macro it copies down the first value of the reference column for the entire destination column:

    Columns("D:D").Select
    Selection.Copy
    Columns("C:C").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False

 

[AussieRob]

Your code for paste special is right but try this:

cells(1,3).select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

You don't need to select the whole col to paste special.

 

[Scott Huish]

I couldn't get your code to fail but you don't need to select the range in order to copy it:

Try this:

Sub copyCtoD()
Range("D:D").Copy
Range("C:C").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

 

[scrupul0us]

That still causes my issue... wtf is goin on here! Is there a way to just convert the original column from calculated values to just the resultant value and discard the formula?

 

[scrupul0us]

I also tried:

    For Each C In Columns("C:C").SpecialCells(xlCellTypeConstants, 3)
    C.Value = C.Offset(0, 1).Value
    Next C

But it says no cells found..

To me that says for each C (row) on column C set the value of the current cell to the value of its neighbor one over...

 

[scrupul0us]

aussie: what does:

cells(1,3).select

imply... whats the 1,3 represent?

 

[AussieRob]

Cells(1,3) is the easy way to reference C1. 1 being row 1 and 3 being Col. 3.

If you have just copied all the data in Col. D you only need to paste (or paste special as the case may be) into cell C1 to transfer the data.

 

[Eric B]

I'm not getting that error either
As a workaround, you could save it as a .txt file, use the import wizard to reopen the .txt file, and then tell it to save again as an Excel

 

[scrupul0us]

Thats way too much work for something so simple...

There has to be a way todo it with a loop if not with paste special

 

[AussieRob]

If you can phisically do it why dont you do it and record the macro. Then you can just add the code in to your existing code.