pasting values only

[Guest]

While copying and pasting the cell value to another workbook the following
program copies formula instead of values. How to copy value only, without
formula? (range a1:a50 contain formulas)

workbooks.open("abc")
workbooks.open("def")
workbooks("abc").activate
Worksheets(1).Range("a1:a50").Copy
workbooks("def").activate
Worksheets(1).Activate
ActiveSheet.Paste Destination:=Worksheets("SHEET1").Cells(2, 1)

 

[Guest]

Substitute this in place of the last line:
Range("A2").PasteSpecial xlValues

You might be interested in a technique that doesn't require opening the
source wb. This macro assumes that the destination wb and wks are both
currently active. A dialog is called that lets you identify the source wb.
Data transfer is achieved via linked formulae. The linked formulae are then
converted to values. It's extremely fast.

Sub TransferData()
Dim pos As Integer
Dim p As String
Dim fn As Variant

fn = Application.GetOpenFilename("Excel Files(*.xls), *.xls")
If fn = False Then Exit Sub
pos = InStrRev(fn, "\")
p = Left(fn, pos - 1)
fn = Right$(fn, Len(fn) - pos)
With ActiveSheet.Range("A2:A51")
.Formula = "='" & p & "\[" & fn & "]Sheet1'!A1"
.Value = .Value
End With
End Sub

Regards,
Greg