Copy|Paste Special|Transpose (2003)

**jlkirk** · 2009-02-20 14:44

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.

**HansV** · 2009-02-20 15:06

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

**wdwells** · 2009-02-20 15:08

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.

**jlkirk** · 2009-02-20 15:23

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

**HansV** · 2009-02-20 15:31

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

**wdwells** · 2009-02-21 00:05

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?

**jlkirk** · 2009-02-21 00:46

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...

**wdwells** · 2009-02-21 12:12

<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?

**jlkirk** · 2009-02-21 12:39

Yes it would be!
Thanks.

**wdwells** · 2009-02-21 12:57

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?

**jlkirk** · 2009-02-21 14:05

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...

**wdwells** · 2009-02-21 14:37

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?

**jlkirk** · 2009-02-21 14:38

If you wish

**wdwells** · 2009-02-21 19:40

Try the attached code.

**jlkirk** · 2009-02-22 17:03

Thanks.

Thread: Copy|Paste Special|Transpose (2003)

Thread Tools