Hi!

When I try to write this command in a macro :

sheets("S1").range("A5:E5).copy (sheets("S2").range("A10:E20"))

occurs an Error "Run-time error '1004': Impossible to execute the copy
method in range class". It seems to me the copy command only works well when
target sheet is the current sheet, I think... (It works when I try to copy to
the sheet where the code is executed)
Is there a way to do this? I need to copy the value and the format os the
cells in the range...

Thanks

Sheets("Sheet1").Range("A5:E5").Copy (Sheets("Sheet2").Range("A10:E20"))
or
Sheets(1).Range("A5:E5").Copy (Sheets(2).Range("A10:E20"))

Works for me.
--
HTH
Roger
Shaftesbury (UK)
(Office 2003, Win XP/SP2)



"Mark Hezz" <Mark (E-Mail Removed)> wrote in message
news:43B02A71-70A4-4EB8-8DC8-(E-Mail Removed)...
> Hi!
>
> When I try to write this command in a macro :
>
> sheets("S1").range("A5:E5).copy (sheets("S2").range("A10:E20"))
>
> occurs an Error "Run-time error '1004': Impossible to execute the copy
> method in range class". It seems to me the copy command only works well
> when
> target sheet is the current sheet, I think... (It works when I try to copy
> to
> the sheet where the code is executed)
> Is there a way to do this? I need to copy the value and the format os the
> cells in the range...
>
> Thanks

It probably don't like the missing quote mark and second set of Parentheses.
Try:

Sheets("S1").Range("A5:E5").copy Sheets("S2").Range("A10")

You only need a reference cell in the top left of the range you want to
paste to.

"Mark Hezz" wrote:

> Hi!
>
> When I try to write this command in a macro :
>
> sheets("S1").range("A5:E5).copy (sheets("S2").range("A10:E20"))
>
> occurs an Error "Run-time error '1004': Impossible to execute the copy
> method in range class". It seems to me the copy command only works well when
> target sheet is the current sheet, I think... (It works when I try to copy to
> the sheet where the code is executed)
> Is there a way to do this? I need to copy the value and the format os the
> cells in the range...
>
> Thanks

I found a solution:

sheets("Sheet1").Range("A5:E5").copy Destination:=
sheets("Sheet2").Range("A10")

In this form, it works in my MS Excel 2003.

Thanks All