Results 1 to 15 of 15
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Copy|Paste Special|Transpose (2003)

    Problem: Trying to copy, paste special and transpose a range of cells from one file to another. If the cells I want to copy, etc., in the source file (i. e., the file from which I want to copy) contains numbers or letters (as opposed to references to other cells), everything is ok. However, whenever source cells have a reference to another cell, wen I try and paste special|transpose, I get blanks for each cell that has such a reference when I paste special|transpose the cells into the target file. Any ideas?
    Thanks in advance.

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

    Re: Copy|Paste Special|Transpose (2003)

    Tick the "Values" check box as well as the "Transpose" check box in the Paste Special dialog.

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copy|Paste Special|Transpose (2003)

    Are you certain that you are getting a blank? Or is the cell referencing a blank cell? Using R1C1 notation, the location of the copied and pasted cell changes as a consequence of the transposition, but the formula remains unchanged.

    H.T.H.
    Regards
    Don

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Copy|Paste Special|Transpose (2003)

    Thanks, Hans. However, I would really like to have the link.
    Jeff

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

    Re: Copy|Paste Special|Transpose (2003)

    You can't do that with Paste Special | Transpose...
    You'll have to create new formulas in the target worksheet.

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copy|Paste Special|Transpose (2003)

    Does the range that is being copied in the source file refer to any cells on another sheet?

    >>However, I would really like to have the link.
    Do you wish to maintain a link back to the source document?
    Or do you wish to refer to the same address on the target sheet?
    Regards
    Don

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Copy|Paste Special|Transpose (2003)

    Yes, some of the cells contain links/references to other locations on the same worksheet, references on other to other worksheets to files that may or may not be open...

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copy|Paste Special|Transpose (2003)

    <big> IF </big> I can achieve this for you, all transposed cells will have any relative references converted to absolute. Will this be of any use to you?
    Regards
    Don

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Copy|Paste Special|Transpose (2003)

    Yes it would be!
    Thanks.

  10. #10
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copy|Paste Special|Transpose (2003)

    I think that your objective will be realized if instead of copying the source into a transposed range in a target workbook; we have the transposed range in the target workbook refer back to the source cells.

    Do you agree?
    Regards
    Don

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Copy|Paste Special|Transpose (2003)

    I agree, but I have quite a few of these ranges I want to cut, paste, etc., and to do as you suggest would be a long and arduous task. was just looking for a short-cut or a more efficient way...

  12. #12
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copy|Paste Special|Transpose (2003)

    I will attempt to automate the process for each range. You would select the range in the source document; select the top left corner of the target range; then run the macro. This will require that only two files be open. Do you want me to proceed?
    Regards
    Don

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Copy|Paste Special|Transpose (2003)

    If you wish

  14. #14
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copy|Paste Special|Transpose (2003)

    Try the attached code.
    Attached Files Attached Files
    Regards
    Don

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Copy|Paste Special|Transpose (2003)

    Thanks.

Posting Permissions

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