Paste as Transposed Link

[Murtaza]

How can i paste cells as link but transpose.

While using Paste Special and check Transpose the Paste Link button is
disabled.

Any Solution?

Best regards,
Murtaza

 

[rodger]

if you want paste cells as link,why do you select transpose

 

[Murtaza]

The Data is horizontal in Sheet1 like:

Units 11 25 35 49 58 60

and i want to link these Units numbers but in vertical format in Sheet2
like:

Units
11
25
35
49
58
60

I cant do this manually becuase there are lots of numbers.
hope this will clarify my query.

Murtaza

 

[Andy Brown]

I cant do this manually becuase there are lots of numbers.

hope this will clarify my query.

Dave Peterson has answered this before, I've no idea how it works.

Copy your horizontal range. Paste Link it on Sheet 2 (at B1 perhaps, to
leave a blank column).

With the pasted range still selected, hit CTRL+H. Replace "=" with "$$$$$="
(no quotes, Replace All).

Copy again, Paste Special -- Transpose.

CTRL+H again, replace "$$$$$=" with "=" (Replace All). Dump the dummy range.

Rgds,
Andy

 

[Larry McFadden]

Hi, Murtaza. I had a similar situation in one of my workbooks, and I used
the following macro to accomplish this task:

Sub CensusLink()


' Keyboard Shortcut: Ctrl+c
'
Selection.Copy
Sheets("Source").Select
ActiveSheet.Paste Link:=True
ActiveCell.Offset(1, 0).Range("A1").Select
Sheets("Target").Select
ActiveCell.Offset(0, 1).Range("A1").Select
End Sub

You will need to edit the "Source" and "Target" sheet names with the
respective names of your own sheets. Make sure you have the cursor
positioned in the first cell of the target sheet where you want the first
data item to be copied. Then go to the source sheet and place the cursor in
the cell containing the first item to be copied. Now hold down the Ctrl key
and press c however many times to copy the entire row to the column on the
target sheet. For a large row of data, you can just hold the c key down,
and it will run the macro repeatedly and very rapidly. But if you overshoot
the range, you can always go back to your target sheet and delete the
results of the extra runs. Hope this helps.

Larry

 

[Larry McFadden]

Sorry, but I got the "Source" and "Target" sheets reversed in the preceding
macro. It should be as follows:

Sub CensusLink()


' Keyboard Shortcut: Ctrl+c
'
Selection.Copy
Sheets("Target").Select
ActiveSheet.Paste Link:=True
ActiveCell.Offset(1, 0).Range("A1").Select
Sheets("Source").Select
ActiveCell.Offset(0, 1).Range("A1").Select
End Sub

Sorry about the mistake.

Larry