Range COPY function - how to copy VALUES and not formulas

[James Cooke]

I want to use the Copy function to copy the values of a range to a
destination range.

I know that you can do this using copy/paste - as in this example:

Rows("1:1009").Select
Selection.Copy
Sheets("rptTemplate_Report").Select
'Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
False, Transpose:=False

But I do not want to use this method, since I have 2 range objects - as in
this example:

Set InputRange = Workbook1.Sheets("Sheet1").Range("Range1")
Set TargetRange = Workbook2.Sheets("Sheet1").Range("Range2")
InputRange.Copy TargetRange

This works, but the result fails because this Copy() copies formulas instead
of values.
How do I do that?
thanks

 

[Ron de Bruin]

If the ranges have the same size you can use the value property

TargetRange.Value = InputRange.Value


You can use resize to make the TargetRange the same size like this

Set SourceRange = Sheets("Sheet1").Range("A1:c10")
With SourceRange
Set destrange = Sheets("Sheet2").Range("A1"). _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = SourceRange.Value