Help with formula

[leerem]

Hi, i need your help with the following;
I'm attempting to copy row A11:Y11 to A12:Y by the number of columns in
Data1 Row "A"

Lastrow = Worksheets("Data1").Cells(Rows.Count, "A").End(xlUp).Row
Worksheets("Calculations").Range("A11:Y11").Copy
Worksheets("Calculations").Range("A12" & Lastrow). Paste:=xlNormal

Therby it should copy cells a11:Y11 to A12:Y59 if Lastrow were to = 47
(if I've got that right)

How have I cocked up with this formula??
Regards
Lee

 

[Mike H]

Try,

Lastrow = Worksheets("Data1").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Calculations").Range("A11:Y11").Copy
Sheets("Calculations").Range("A12:A" & Lastrow + 12).PasteSpecial

Mike

 

[leerem]

Hi Mike;

The cells copied over but than halted with an error msg of
'PasteSpecial method of Range Class failed'

Lastrow = Worksheets("Data1").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Calculations").Range("A11:Y11").Copy
Sheets("Calculations").Range("A12:A" & Lastrow + 10).PasteSpecial
Paste:=xlNormal, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

any ideas

 

[Mike H]

Hi,

I'm afraid i can't replicate that error, the code I gave you and your
modified version work fine for me.

Mike

 

[Sandy Mann]

Mike,

Could it be the unfortunate line break?

Lastrow = Worksheets("Data1").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Calculations").Range("A11:Y11").Copy
Sheets("Calculations").Range("A12:A" & Lastrow + 10).PasteSpecial _
Paste:=xlNormal, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk