Hello,

I am looking to Copy a cell then paste n rows lower. I would like this
number n to be a value in a cell. i.e. If cell A1 = 2, then copy B1 two rows
lower (i.e. to B3). If cell A1=4, then copy B1 four rows lower (i.e. to B5).

I know offset ( , ) should be used for this, but as a novice I don't seem to
be able to put the cell reference in i.e. offset (A1,0)

Any help would be appreciated!
thanks
Stephen, London

You didn't post your macro so far

Sub copydestbasedoncell()
Range("c13").Copy Range("c13").Offset(Range("a1"))
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"StephenT" wrote in message
...
Hello,

I am looking to Copy a cell then paste n rows lower. I would like this
number n to be a value in a cell. i.e. If cell A1 = 2, then copy B1 two
rows
lower (i.e. to B3). If cell A1=4, then copy B1 four rows lower (i.e. to
B5).

I know offset ( , ) should be used for this, but as a novice I don't seem
to
be able to put the cell reference in i.e. offset (A1,0)

Any help would be appreciated!
thanks
Stephen, London

Hi,

try this

Range("A1").Offset(Range("A1").Value - 1, 1).Value = Range("A1").Value

so if A1 contains a 4, that value is copied to B4

Mike

"StephenT" wrote:

Hello,

I am looking to Copy a cell then paste n rows lower. I would like this
number n to be a value in a cell. i.e. If cell A1 = 2, then copy B1 two rows
lower (i.e. to B3). If cell A1=4, then copy B1 four rows lower (i.e. to B5).

I know offset ( , ) should be used for this, but as a novice I don't seem to
be able to put the cell reference in i.e. offset (A1,0)

Any help would be appreciated!
thanks
Stephen, London

with activesheet
.range("B1").copy _
destination:=.range("b1").offset(.range("a1").valu e,0)
end with

There are no validity checks here. If you have a number too small (negative) or
too large or even text in A1, then it'll blow up.

StephenT wrote:

Hello,

I am looking to Copy a cell then paste n rows lower. I would like this
number n to be a value in a cell. i.e. If cell A1 = 2, then copy B1 two rows
lower (i.e. to B3). If cell A1=4, then copy B1 four rows lower (i.e. to B5).

I know offset ( , ) should be used for this, but as a novice I don't seem to
be able to put the cell reference in i.e. offset (A1,0)

Any help would be appreciated!
thanks
Stephen, London

--

Dave Peterson