Hello All,

Another problem I have is in automating the number of rows down wit
which I copy a range of cells.

At present in the macro I have this bit:

Range("T5:AC5").Select
Application.CutCopyMode = False
Selection.Copy
Range("T6:T1008").Select
ActiveSheet.Paste

now rather than '1008' I want the 1008 bit to be a number that appear
in cell T1 (the number is different from sheet to sheet).

How do I do that?

Regards,
Jaime

--
jaimetimbrel
-----------------------------------------------------------------------
jaimetimbrell's Profile: http://www.excelforum.com/member.php...fo&userid=2616
View this thread: http://www.excelforum.com/showthread.php?threadid=51160

One way:

Range("T5:AC5").Copy _
Destination:=Range("T6").Resize(Range("T1").Value, 10)

In article
,
jaimetimbrell
wrote:

Hello All,

Another problem I have is in automating the number of rows down with
which I copy a range of cells.

At present in the macro I have this bit:

Range("T5:AC5").Select
Application.CutCopyMode = False
Selection.Copy
Range("T6:T1008").Select
ActiveSheet.Paste

now rather than '1008' I want the 1008 bit to be a number that appears
in cell T1 (the number is different from sheet to sheet).

How do I do that?

Regards,
Jaime.

Thank you very much for your help.

It works well. I did notice it copies the formulae down 1 row too many
- could that be corrected?

Regards,
Jaime.


--
jaimetimbrell
------------------------------------------------------------------------
jaimetimbrell's Profile: http://www.excelforum.com/member.php...o&userid=26162
View this thread: http://www.excelforum.com/showthread...hreadid=511609

One extra row doesn't make much sense. Either the value in T1 represents
the number of rows or the row to stop on. In the current case, the code is
written for the number of rows. See this demo from the immediate window
(with 1008 in T1)

? Range("T6").Resize(Range("T1").Value, 10).Address
$T$6:$AC$1013
? Range("T1").Value
1008
? Range("T6").Resize(Range("T1").Value - 5, 10).Address
$T$6:$AC$1008

adjust your code to fit what you want.

--
Regards,
Tom Ogilvy


"jaimetimbrell"
wrote in message
news:jaimetimbrell.235r1n_1139817316.661@excelforu m-nospam.com...

Thank you very much for your help.

It works well. I did notice it copies the formulae down 1 row too many
- could that be corrected?

Regards,
Jaime.


--
jaimetimbrell
------------------------------------------------------------------------
jaimetimbrell's Profile:
http://www.excelforum.com/member.php...o&userid=26162
View this thread: http://www.excelforum.com/showthread...hreadid=511609

Tom,

Thanks for your help, I meant that the value in T1 was the total number
of rows I needed, so when I was applying it to the copy range I was
getting 1 row too many. When I was reading your solution I had a
brainwave (which for me was more of a brainripple!) and adjusted the
destination range in the original solution from 'T6' to T5' thereby
including the first cell and getting the correct number of rows.

I appreciate your help.

Regards,
Jaime.


--
jaimetimbrell
------------------------------------------------------------------------
jaimetimbrell's Profile: http://www.excelforum.com/member.php...o&userid=26162
View this thread: http://www.excelforum.com/showthread...hreadid=511609