Share via

Excel 2013 Standard - inbuilt & vba copy/paste transpose function broken

Anonymous
2020-06-02T03:24:02+00:00

EDIT: This is an issue in MS 2013 standard, but not MS 2013 professional.

Simple copy & paste with transpose function are broken.

Both in Excel & in VBA 2013. Worked fine in 2010.

Steps to reproduce in Excel.

Make A1 to A10 = 1,2,3,4,5,6,7,8,9,10.

Copy & paste link to column C. So column A & C are same, with C links to A.

Copy C1:C10 & transpose anywhere. In built Excel function, or vba transpose function, fails & is trying to transpose, with links to, the original data in Column A. This wasn't the behavior previously.

The 2nd copy/paste with transpose is also creating a link, even though excel greys out & disables "Paste Link" option.

Steps to reproduce in vba.

rangeSource.copy

rangeA.Select

Paste Link:=True

rangeA.copy

rangeB.PasteSpecial Transpose:=True

I have a spreadsheet that uses these vba calls for a large range, but it's all mangled now. The first 7 items of rangeB link correctly to rangeA (which is linnked to rangeSource), but then the rest of rangeB links back to the original source data all out of order.

I have checked the addresses of rangeA & rangeB & they are correct.

The behavior has obviously been changed, it didn't previously link like this.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-06-02T23:53:08+00:00

    Seems this is an issue in MS 2013 standard. I've checked using 2013 professional & the issue isn't there, using/doing the exact same thing.

    This is just copy & paste once, no vba.

    https://imgur.com/yhWmKEL

    First off, it is important to note that there is no range.PasteSpecial Link:=True command. While range.Paste Link:=True is listed in the quick range.PasteSpecial right-click menu options, it is a range.Paste command; not a range.PasteSpecial command.

    I don't know if this 'behaviour' has changed fom xl2010 to xl2013 but the default xl2013 behaviour for range.PasteSpecial Paste:=xlPasteAll, Transpose:=True does seem to 'remember' the behaviour of the previous range.Paste Link:=True command and try to paste links instead of values. If you explicitly use range.PasteSpecial Paste:=xlPasteValues, Transpose:=True then range.PasteSpecial 'remembers' that and subsequent range.PasteSpecial Paste:=xlPasteAll, Transpose:=True work as expected.

    1. Put 1 through 10 in A1:A10.
    2. Copy.
    3. Select C1 and Paste Link:=True.
    4. Copy C1:C10.
    5. Select D2 and Paste Special, Transpose (doesn't work)
    6. Select D3 and Paste Special, Values, Transpose (works!).
    7. Select D2 and Paste Special, Transpose (works!)

    Code:

    dim rangeSource as range, rangeC as range, rangeD as range

    Set rangeSource = Range("A1:A10")

    Set rangeC = Range("C1:C10")

    Set rangeD = Range("D2")

    rangeSource.Copy

    rangeC.Paste Link:=True

    'doesn't work

    rangeD.PasteSpecial Paste:=xlPasteAll, Transpose:=True

    'works!

    rangeD.PasteSpecial Paste:=xlPasteValues, Transpose:=True

    'works!

    rangeD.Offset(1, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True

    So the moral of the story is not to rely on default behaviour or catch-alls like Paste:=xlPasteAll since they may 'remember' something that you don't want. That goes double for coding where you have no idea what the user has previously done on the worksheet during the current editing session (see range.Find command).

    I didn't use range.PasteSpecial Link:=True.

    Your examples don't link. Plus you're missing a step & it's wrong here;

    rangeSource.Copy

    rangeSource.Select <<your example is missing this, which is why your example doesn't work.

    rangeC.Paste Link:=True <<plus this is wrong. I used just Paste Link:=True, not rangeC.Paste. Which also contributes to your example not working.

    I need it to link, your examples don't provide this, problem is the linking mechanism is mangling & not pasting properly when it transposes once it passes. And it's worse on a larger dataset. And clearly behaviour has changed as I've now been able to check against 2 versions of MS 2013. One works as expected, as it did in 2010, the other doesn't. Again see the image link I posted.

    2 people found this answer helpful.
    0 comments
  2. Anonymous
    2020-06-02T07:06:57+00:00

    First off, it is important to note that there is no range.PasteSpecial Link:=True command. While range.Paste Link:=True is listed in the quick range.PasteSpecial right-click menu options, it is a range.Paste command; not a range.PasteSpecial command.

    I don't know if this 'behaviour' has changed fom xl2010 to xl2013 but the default xl2013 behaviour for range.PasteSpecial Paste:=xlPasteAll, Transpose:=True does seem to 'remember' the behaviour of the previous range.Paste Link:=True command and try to paste links instead of values. If you explicitly use range.PasteSpecial Paste:=xlPasteValues, Transpose:=True then range.PasteSpecial 'remembers' that and subsequent range.PasteSpecial Paste:=xlPasteAll, Transpose:=True work as expected.

    1. Put 1 through 10 in A1:A10.
    2. Copy.
    3. Select C1 and Paste Link:=True.
    4. Copy C1:C10.
    5. Select D2 and Paste Special, Transpose (doesn't work)
    6. Select D3 and Paste Special, Values, Transpose (works!).
    7. Select D2 and Paste Special, Transpose (works!)

    Code:

    dim rangeSource as range, rangeC as range, rangeD as range

    Set rangeSource = Range("A1:A10")

    Set rangeC = Range("C1:C10")

    Set rangeD = Range("D2")

    rangeSource.Copy

    rangeC.Paste Link:=True

    'doesn't work

    rangeD.PasteSpecial Paste:=xlPasteAll, Transpose:=True

    'works!

    rangeD.PasteSpecial Paste:=xlPasteValues, Transpose:=True

    'works!

    rangeD.Offset(1, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True

    So the moral of the story is not to rely on default behaviour or catch-alls like Paste:=xlPasteAll since they may 'remember' something that you don't want. That goes double for coding where you have no idea what the user has previously done on the worksheet during the current editing session (see range.Find command).

    0 comments