Thread: Copy Values not Formulas

Hi,
Is there any way to copy a cell to another sheet as a Value, not as a formula.

I'm using this method of copy for I need to create a list of all the datas from many worksheet in one.

Code:

For J = 2 To Sheets.Count
    Sheets(J).Activate 
    Range("B23:B31").Select
    Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next

For B23 is a value and from B24 to B31, it' =(B23)
Using the "Selection.Copy Destination" is there anything I could add to it so that it copies the value?

I don't want to change all my macros, because I've got a couple with this kind of copy..

Any help would be appreciated.

Code:

    For j = 2 To Worksheets.Count
        Worksheets(j).Range("B23:B31").Copy
        Worksheets(1).Cells(Rows.Count, "A").End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
    Next j

Would this work if I have multiple range i have to copy? I might not have been clear enough.. here's my whole macro.

Code:

Dim J As Integer

    On Error Resume Next
Application.DisplayAlerts = False
Sheets("Résumé").Delete
    Count_Sheets = ThisWorkbook.Sheets.Count
    Sheets.Add Before:=Sheets(1), Type:="C:\Documents and Settings\admin\Application Data\Microsoft\Modèles\resume.xlt"
    Sheets(1).Name = "Résumé"
    ActiveSheet.Range("I1").Value = Count_Sheets
    ' work through sheets
    For J = 2 To Sheets.Count ' from sheet 2 to last sheet
    Sheets(J).Activate ' make the sheet active
    Range("B23:B31").Select
    Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
    'Marque
    Range("D23:D31").Select
    Selection.Copy Destination:=Sheets(1).Range("B65536").End(xlUp)(2)
    'Description Produit
    Range("E23:E31").Select
    Selection.Copy Destination:=Sheets(1).Range("C65536").End(xlUp)(2)
    'No Série
    Range("F23:F31").Select
    Selection.Copy Destination:=Sheets(1).Range("D65536").End(xlUp)(2)
    'Étiquette
    Range("G23:G31").Select
    Selection.Copy Destination:=Sheets(1).Range("E65536").End(xlUp)(2)
    'Succ
    Range("c1:c9").Select
    Selection.Copy Destination:=Sheets(1).Range("g65536").End(xlUp)(2)
    'Kit
    Range("f10:f18").Select
    Selection.Copy Destination:=Sheets(1).Range("h65536").End(xlUp)(2)
    Next
End Sub

From B to G, everything if fine, everything is typed manually, but C1:C9 and F10:F18 are formulas.

So i'd like to copy the value not the formulas from these two. c1:c9 & f:10f:18

So, try it.

In the example I posted, I eliminated the Selects. If you applied that throughout, you woudn't see any 'flipping'