Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell Movement (XP; SR3)

    Why does the activecell change to one position to the right if I am only trying to copy the formula?

    Sub TestCopyFormula()
    ActiveCell.Copy
    ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    ActiveCell.Offset(1, 0).Select
    End Sub

    I'm confused.

    Thanks,
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell Movement (XP; SR3)

    If you single-step through the macro using F8, you'll see that PasteSpecial not only pastes, but also selects the target cell. You can make sure that the cell below the original cell is selected as follows:

    Sub TestCopyFormula()
    Dim rng As Range
    Set rng = ActiveCell
    rng.Copy
    rng.Offset(0, 1).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    rng.Offset(1, 0).Select
    Set rng = Nothing
    End Sub

    Note: in many cases, it's not necessary to shift the selection.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Movement (XP; SR3)

    I wasn't quit sure if the "paste" feature was actually suppose to select the cell.

    Your suggested code works great.

    Many thanks,
    John

Posting Permissions

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