copy to destination syntax

  • Hi,


    Im trying to improve my code by replacing copy and paste with copy destination.


    However i cant seem to get the syntax right.


    The error message is "expected list seperator or )", could someon please tell me where i am going wrong?


    Cheers
    Neil

  • Re: copy to destination syntax


    Hi Neiluk66,


    Try this:


    [vba]
    Sub test3()
    With Worksheets("sheet1").Range("a1")
    .End(xlDown).Copy Destination:=Worksheets("sheet2").Range("A1")
    End With
    End Sub
    [/vba]


    HTH.

  • Re: copy to destination syntax


    Thanks for the reply.


    Your ammendment does copy cell A1 to sheet2 but does not paste the entire range on sheet 1, just cell a1.


    Is this a problem with using the destination Range("A1")?


    Any ideas would be appreciated.


    thanks

  • Re: copy to destination syntax


    Hello Neil,


    Possibly, you want...

    Code
    Worksheets("sheet1").Range("a1:a" & Range("a1").End(xlDown).Row).Copy _
            Destination:=Worksheets("sheet2").Range("A1")


    or

    Code
    Worksheets("sheet1").Range("a1:a" & Range("a65536").End(xlUp).Row).Copy _
            Destination:=Worksheets("sheet2").Range("A1")

    If I've been helpful, let me know. If I haven't, let me know that too. 

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!