Mind's gone blank!

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

Not done recently and cannot remeber how to do it directly something like
this
Range(Cells(4, 6), Cells(45, 6)) = Range(Cells(4, 2), Cells(45, 2))
which of course does not work
and, anyway, I want Values only copied across

Help please

Francis Hookham

Many thanks Garry's Student

Any reason why I should not simplify it to:

Range(Cells(4, 2), Cells(45, 2)).Copy
Range(Cells(4, 6), Cells(45, 6)).PasteSpecial xlPasteValues

which works.

I had hoped for something whch did not go and copy and then paste - surely I
have seen/used in the past something like 'CopyDestination', as in
Range(Cells(4, 2), Cells(45, 2)).CopyDestination = Range(Cells(4, 6),
Cells(45, 6))

(bear in mind that Range(Cells(4, 2), Cells(45, 2)) contains '=Present!R'
collecting data from another worksheet which is why PasteSpecial
xlPasteValues is needed.

Am I expecting too much?

Francis Hookham


"Gary''s Student" wrote in message
...
You were almost the

Sub francis()
Dim r1 As Range, r2 As Range
Set r1 = Range(Cells(4, 6), Cells(45, 6))
Set r2 = Range(Cells(4, 2), Cells(45, 2))
r2.Copy
r1.PasteSpecial xlPasteValues
End Sub


--
Gary''s Student


"Francis Hookham" wrote:

Mind's gone blank!

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

Not done recently and cannot remeber how to do it directly something like
this
Range(Cells(4, 6), Cells(45, 6)) = Range(Cells(4, 2), Cells(45, 2))
which of course does not work
and, anyway, I want Values only copied across

Help please

Francis Hookham

Actually
Range(Cells(4, 2), Cells(48, 2)).Copy
Cells(4, 6).PasteSpecial xlPasteValues
is more economical

FH


"Gary''s Student" wrote in message
...
You were almost the

Sub francis()
Dim r1 As Range, r2 As Range
Set r1 = Range(Cells(4, 6), Cells(45, 6))
Set r2 = Range(Cells(4, 2), Cells(45, 2))
r2.Copy
r1.PasteSpecial xlPasteValues
End Sub


--
Gary''s Student


"Francis Hookham" wrote:

Mind's gone blank!

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

Not done recently and cannot remeber how to do it directly something like
this
Range(Cells(4, 6), Cells(45, 6)) = Range(Cells(4, 2), Cells(45, 2))
which of course does not work
and, anyway, I want Values only copied across

Help please

Francis Hookham