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

 

[scrupul0us]

Aussie your method gives me the same problem as well very freaking wierd

 

[scrupul0us]

maybe if i post the entire sub:

Private Sub address()
'-------------------------------------->
'
'
'
'-------------------------------------->
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual   'pre XL97 xlManual
    
    For Each C In Columns("C:C").SpecialCells(xlCellTypeConstants, 3)
    C.Value = C.Value & C.Offset(0, 1).Value
    Next C
    
    Columns("D:D").Select
    Selection.ClearContents
    Columns("C:C").EntireColumn.AutoFit
    
    Range("D1").Select
    ActiveCell.FormulaR1C1 = _
    "=IF(RC[-1]="""","""",IF(ISNUMBER(RIGHT(RC[-1],5)*1),RIGHT(RC[-1],5)*1,RIGHT(RC[-1],4)*1))"
    Range("D1").Select
    Selection.AutoFill Destination:=Columns("D:D"), Type:=xlFillDefault
    Selection.NumberFormat = "00000"
    Columns("D:D").EntireColumn.AutoFit
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[1]="""","""",IF(ISNUMBER(RIGHT(RC[1],5)*1),LEFT(RC[1],LEN(RC[1])-5),LEFT(RC[1],LEN(RC[1])-4)))"
    Range("C1").Select
    Selection.AutoFill Destination:=Columns("C:C"), Type:=xlFillDefault
    Columns("C:C").Select
    Columns("C:C").EntireColumn.AutoFit
    Selection.Insert Shift:=xlToRight

    Columns("D:D").Select
    Selection.Copy
    Cells(1, 3).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[AussieRob]

There needs to be a _ between Paste:=xlPasteValues, and Operation:=xlNone in your paste special code.

But I dont know whats going on. I'll see if i can come up with something.

 

[scrupul0us]

Selection.PasteSpecial Paste:=xlPasteValues, _Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Like this


and for what reason? its all on one line in my VBA editor

 

[AussieRob]

No sorry, you need to have _ when you break the code by going down a line.

Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

or

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

or

Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

 

[Scott Huish]

If it's all on the same line you don't need a _
You don't have to break up any of those things, they can all go on the same line.

 

[AussieRob]

I know but it looks nicer and I find it easy to read when its broken up.

Its just a personal pref.

 

[scrupul0us]

i did... thats whatim say... when i manually do it (while its recording) it works just fine... once i add it, it breaks

 

[parry]

Hi scrupul0us, have you advised what the error is? Recently, I was frustrated with a paste special method coming up with error 1004 regarding the objects needing to be the same size. The objects were the same size but the range I was copying from contained merged cells. Even when amending the destination cells to have merged cells in exactly the same position as the source the code still failed. I believe this is a bug with paste special and to get around this simply use Range(xx).Value = Range(yy).Value in order to pastespecial values.

When copying or pasting I usually only use one cell reference for the destination range so you dont have to worry about whether youve put in a range for the correct size.

 

[erik.van.geit]

Hi, guys,

Once I replaced "columnwidths" by an "8": on some computers problems were then solved
in the same way you can replace "xlPasteValues" by a "3" (I just found by trial and error: it must be somewhere in the help-files)

Selection.PasteSpecial Paste:=3, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

does this help ?

regards,
Erik
(just passing by here)