General discussion

Locked

Paste Special Macro

By ljackson.desoto ·
Hello -
I need to do a bunch of Paste Specials with the option to transpose data. Under customize I know you can choose Paste Special, but then you still have to select transpose each time. Anybody know of a macro for Paste Special Transpose? OR an easy way to move data from rows to columns. For example:

ABC Company
1234 Main Street
Anytown, USA 02021
(the data is in rows)

needs to be

ABC Company 1234 Main Street Anytwown, USA 02021

(data needs to be in columns)

Unless, you can mail merge from rows instead of columns. HELP!

Thanks!

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by Ltop In reply to Paste Special Macro

Hi,

Depending how much data were talking about, and if after it is columns it will be managed in columns thereafter.

A normal mail merge needs to have the column headings so have you got row identifiers?

Is all the data structured like your example, 3 rows for each contact, or are they different.

An IF statement like =IF(A1="name",B1," "), if nested refering to your row identifiers, could populate the columns, then you could do a paste special (values) and sort to get rid of the blank lines and the data would be ready for a mail merge.

let us know how u get on, or if you want more info or an example,

cheers

Collapse -

by ljackson.desoto In reply to

Poster rated this answer.

Collapse -

by chitosunday In reply to Paste Special Macro

You can connect all the data into one using "&"
for example your data is in a1 to a4 then create a formula like this
=a1&" "&a2&" "&a3&" "&a4

Collapse -

by chitosunday In reply to

or this formula
=CONCATENATE(A1," ",A2," ",A3," ",A4)

Collapse -

by chitosunday In reply to

Sorry i did not read your requirement completely.
Put this macro code in the module and assign a short cut key like ctrl a. First copy the data then press ctrlA . The data will transpose.

sub pastetranspose()
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
end sub

Collapse -

by ljackson.desoto In reply to

Poster rated this answer.

Collapse -

by ljackson.desoto In reply to Paste Special Macro

This question was closed by the author

Back to Software Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums