Software

Ask something
Discussion Locked

Paste Special Macro

0 helpful
7 replies

by ljackson.desoto

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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!

7 replies

by ltop

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

by chitosunday

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

by chitosunday

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

This discussion is closed to new replies.