VBA: copy of a cells value disregarding the cells formula

[NEAnthony]

Hi

Picture you have some cells displaying a number based on a formula. When a copy of this cell is made and you then whant to paste only the value into another cell, you use the paste special option i excel.

My question is how do i write this code in VBA? I like to make a button when clicked it copies only the value from one cell to another disregarding that the value is based upon a formula.

Thanks in advance.

 

[Jon von der Heyden]

Hello

Here is an example:

Range("A1").Copy
    Range("A2").PasteSpecial xlValues

 

[SteveO59L]

Range("E3").Select
Selection.Copy
Range("F3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

[yytsunamiyy]

2 possibilities:

1) Use the pastespecial method - See XL help file for explanation
2) Write the code so that:
Range(Your Target range).value = Range(Your Source range).value

 

[doofusboy]

Assuming the cell you want to copy is A1 and where you want to copy it is B1:

    Range("A1").Select
    Selection.Copy
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteValues

When you need something like this, try recording a macro and it will give you the code you need.

 

[NEAnthony]

Hi

Thanks for all your input.

I stil have a problem. I have following code
Private Sub CommandButton1_Click()
Sheets("Sheet2").Select
Range("C47:AA47").Select
Selection.Copy
Sheets("FPY på Panel").Select
Range("A8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

At Range("C47:AA47").Select the program stops with a runtime error '1004' - Select method of Range class failed. What does this mean?

 

[Jon von der Heyden]

Let's avoid the select, I hope that will overcome the error. Try;

Private Sub CommandButton1_Click()
    Sheets("Sheet2").Range("C47:AA47").Copy
    Sheets("FPY på Panel").Range("A8").PasteSpecial xlValues
End Sub

 

[NEAnthony]

It works! Thanks a million time!!