[RESOLVED] Pastespecial xlPasteValues same column

[Radjesh Klauke]

Hi there,

Column I has formulas (=G1+H1) which need to become values. What I was thinking was to simply copy the column and use pastespecial to paste it again.

Columns("I").Copy
Columns("I").PasteSpecial Paste:=xlPasteValues

Nothing seems to happen.
What am, I doing wrong here? Thanks in advance.

[vbfbryce]

Try this:

Sub psvals()
    Range("i1").EntireColumn.Copy
    Range("i1").PasteSpecial xlPasteValues
End Sub

[Radjesh Klauke]

Seems something is going absolutely wrong.

This is my code to add the Formula and then copy/paste it as Value

    With Range("I2")
        .FormulaR1C1 = "=RC[-2]+RC[-1]"
        '.AutoFill Destination:=Range(.Offset(0, -1), .Offset(0, -1).End(xlDown)).Offset(0, 1)
        .AutoFill .Resize(.Offset(Rows.Count - .Row, -1).End(xlUp).Row)
    End With
    
    Range("I1").EntireColumn.Copy
    Range("I1").PasteSpecial xlPasteValues

The complete "usedrange" of column-I get's the value of I1.
Means when !i have the sum of 100, every cell in the "usedrange" of column-I get's the value of 100. Hope I said that right.

What could be the issue?

[vbfbryce]

I'm not following. Can you zip and attach your example?

[Radjesh Klauke]

It has sensitive information, but I can mail it to you of you'd send me you e-mail via PM.

[Siddharth Rout]

1. Are macros enabled?
2. If macros are enabled then you need to fully qualify the sheet names... See this code

Sub Sample()
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    With ws
        .Columns(9).Copy
        .Columns(9).PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
    End With
End Sub

OR

Sub Sample()
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    With ws
        .Range("I:I").Value = .Range("I:I").Value
    End With
End Sub

[Radjesh Klauke]

Completely EDIT POST

The result is the issue again

Talk to you tomorrow.