Thread: macro copy problem

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.

One way:

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

In article
<jaimetimbrell.235mez_1139811301.4351@excelforum-nospam.com>,
jaimetimbrell
<jaimetimbrell.235mez_1139811301.4351@excelforum-nospam.com> 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.

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" <jaimetimbrell.235r1n_1139817316.661@excelforum-nospam.com>
wrote in message
news:jaimetimbrell.235r1n_1139817316.661@excelforum-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.