+ Reply to Thread
Results 1 to 4 of 4

Range.Copy (Destination)

  1. #1
    Garry Douglas
    Guest

    Range.Copy (Destination)

    Hi

    Can someone please explain where I'm going wrong with this simple two line
    chunk of code?

    B7 is the currently selected cell on the active sheet.

    I've assigned the cell reference for D10 to a variable named copycell by
    using the following line:

    Let copycell = Selection.Offset(3,2).Address(False,False)

    All I want to be able to do is copy the contents of D10 (an = sum() formula)
    to E10 using the Range.Copy ([Destination]) method but if I use Range
    (copycell).Copy (Range(copycell).Offset (0,1)) it returns Run Time Error
    1004 (Copy method of Range class failed).

    However, if I use a range object offset from the current cell (B7) as the
    destination i.e. Range (copycell).Copy (Selection.Offset (3,3)) it pastes
    the relative formula from D10 into E10 as required.

    What am I doing wrong?

    Thanks for any suggestions.

    Garry Douglas

    ** Please Remove SPMOFF To Reply **






  2. #2
    Tom Ogilvy
    Guest

    Re: Range.Copy (Destination)

    Sub Tester1()
    Range("B7").Select
    Let copycell = Selection.Offset(3, 2).Address(False, False)
    Range(copycell).Copy Range(copycell).Offset(0, 1)
    End Sub

    worked fine for me. I believe you had an extra pair of parenthesis around

    Range(copycell).Offset(0, 1)

    so it was

    Range(copycell).Copy (Range(copycell).Offset(0, 1))

    That extra pair of parentheses causes the range reference to be
    dereferenced, so it becomes the wrong type argument to the copy command. I
    would expect your second example to have similar problems, but perhaps you
    actually omitted the second set of parentheses and avoided the problem. But
    I definitely get your error message if I enclose the second argument in
    parentheses.

    --
    Regards,
    Tom Ogilvy



    "Garry Douglas" <[email protected]> wrote in message
    news:[email protected]...
    > Hi
    >
    > Can someone please explain where I'm going wrong with this simple two line
    > chunk of code?
    >
    > B7 is the currently selected cell on the active sheet.
    >
    > I've assigned the cell reference for D10 to a variable named copycell by
    > using the following line:
    >
    > Let copycell = Selection.Offset(3,2).Address(False,False)
    >
    > All I want to be able to do is copy the contents of D10 (an = sum()

    formula)
    > to E10 using the Range.Copy ([Destination]) method but if I use Range
    > (copycell).Copy (Range(copycell).Offset (0,1)) it returns Run Time Error
    > 1004 (Copy method of Range class failed).
    >
    > However, if I use a range object offset from the current cell (B7) as the
    > destination i.e. Range (copycell).Copy (Selection.Offset (3,3)) it pastes
    > the relative formula from D10 into E10 as required.
    >
    > What am I doing wrong?
    >
    > Thanks for any suggestions.
    >
    > Garry Douglas
    >
    > ** Please Remove SPMOFF To Reply **
    >
    >
    >
    >
    >




  3. #3
    Garry Douglas
    Guest

    Re: Range.Copy (Destination)

    Tom

    Thanks for the tip. I'll give it a try.

    I also discovered through trial and error (a.k.a. Help) that the Destination
    part of the Range.Copy (Destination) argument could have been expressed as
    Destination:=Range(copycell).Offset(0, 1) which seems to proves your double
    parenthesis theory :-) I assume that the "Destination:=" qualifier is
    optional.

    Regards

    Garry

    "Tom Ogilvy" <[email protected]> wrote in message
    news:[email protected]...
    > Sub Tester1()
    > Range("B7").Select
    > Let copycell = Selection.Offset(3, 2).Address(False, False)
    > Range(copycell).Copy Range(copycell).Offset(0, 1)
    > End Sub
    >
    > worked fine for me. I believe you had an extra pair of parenthesis around
    >
    > Range(copycell).Offset(0, 1)
    >
    > so it was
    >
    > Range(copycell).Copy (Range(copycell).Offset(0, 1))
    >
    > That extra pair of parentheses causes the range reference to be
    > dereferenced, so it becomes the wrong type argument to the copy command. I
    > would expect your second example to have similar problems, but perhaps you
    > actually omitted the second set of parentheses and avoided the problem.
    > But
    > I definitely get your error message if I enclose the second argument in
    > parentheses.
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    >
    > "Garry Douglas" <[email protected]> wrote in message
    > news:[email protected]...
    >> Hi
    >>
    >> Can someone please explain where I'm going wrong with this simple two
    >> line
    >> chunk of code?
    >>
    >> B7 is the currently selected cell on the active sheet.
    >>
    >> I've assigned the cell reference for D10 to a variable named copycell by
    >> using the following line:
    >>
    >> Let copycell = Selection.Offset(3,2).Address(False,False)
    >>
    >> All I want to be able to do is copy the contents of D10 (an = sum()

    > formula)
    >> to E10 using the Range.Copy ([Destination]) method but if I use Range
    >> (copycell).Copy (Range(copycell).Offset (0,1)) it returns Run Time Error
    >> 1004 (Copy method of Range class failed).
    >>
    >> However, if I use a range object offset from the current cell (B7) as the
    >> destination i.e. Range (copycell).Copy (Selection.Offset (3,3)) it
    >> pastes
    >> the relative formula from D10 into E10 as required.
    >>
    >> What am I doing wrong?
    >>
    >> Thanks for any suggestions.
    >>
    >> Garry Douglas
    >>
    >> ** Please Remove SPMOFF To Reply **
    >>
    >>
    >>
    >>
    >>

    >
    >




  4. #4
    Tom Ogilvy
    Guest

    Re: Range.Copy (Destination)

    arguments can be passed by name or by position. If the Destination:= is not
    use, you are passing by position.

    --
    Regards,
    Tom Ogilvy

    "Garry Douglas" <[email protected]> wrote in message
    news:[email protected]...
    > Tom
    >
    > Thanks for the tip. I'll give it a try.
    >
    > I also discovered through trial and error (a.k.a. Help) that the

    Destination
    > part of the Range.Copy (Destination) argument could have been expressed as
    > Destination:=Range(copycell).Offset(0, 1) which seems to proves your

    double
    > parenthesis theory :-) I assume that the "Destination:=" qualifier is
    > optional.
    >
    > Regards
    >
    > Garry
    >
    > "Tom Ogilvy" <[email protected]> wrote in message
    > news:[email protected]...
    > > Sub Tester1()
    > > Range("B7").Select
    > > Let copycell = Selection.Offset(3, 2).Address(False, False)
    > > Range(copycell).Copy Range(copycell).Offset(0, 1)
    > > End Sub
    > >
    > > worked fine for me. I believe you had an extra pair of parenthesis

    around
    > >
    > > Range(copycell).Offset(0, 1)
    > >
    > > so it was
    > >
    > > Range(copycell).Copy (Range(copycell).Offset(0, 1))
    > >
    > > That extra pair of parentheses causes the range reference to be
    > > dereferenced, so it becomes the wrong type argument to the copy command.

    I
    > > would expect your second example to have similar problems, but perhaps

    you
    > > actually omitted the second set of parentheses and avoided the problem.
    > > But
    > > I definitely get your error message if I enclose the second argument in
    > > parentheses.
    > >
    > > --
    > > Regards,
    > > Tom Ogilvy
    > >
    > >
    > >
    > > "Garry Douglas" <[email protected]> wrote in message
    > > news:[email protected]...
    > >> Hi
    > >>
    > >> Can someone please explain where I'm going wrong with this simple two
    > >> line
    > >> chunk of code?
    > >>
    > >> B7 is the currently selected cell on the active sheet.
    > >>
    > >> I've assigned the cell reference for D10 to a variable named copycell

    by
    > >> using the following line:
    > >>
    > >> Let copycell = Selection.Offset(3,2).Address(False,False)
    > >>
    > >> All I want to be able to do is copy the contents of D10 (an = sum()

    > > formula)
    > >> to E10 using the Range.Copy ([Destination]) method but if I use Range
    > >> (copycell).Copy (Range(copycell).Offset (0,1)) it returns Run Time

    Error
    > >> 1004 (Copy method of Range class failed).
    > >>
    > >> However, if I use a range object offset from the current cell (B7) as

    the
    > >> destination i.e. Range (copycell).Copy (Selection.Offset (3,3)) it
    > >> pastes
    > >> the relative formula from D10 into E10 as required.
    > >>
    > >> What am I doing wrong?
    > >>
    > >> Thanks for any suggestions.
    > >>
    > >> Garry Douglas
    > >>
    > >> ** Please Remove SPMOFF To Reply **
    > >>
    > >>
    > >>
    > >>
    > >>

    > >
    > >

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1