On 6 July, 17:00, "Rick Rothstein"
wrote:
Clarification please... you say you have 2560 of these...

What I actually have to process is a spreadsheet full of local areas,
known as "wards". After each ward name is a postcode (Americans call
these "zip codes"), or usually several postcodes. A single row might
look like this:

Barton Ward CT6 5RT, CT6 4JL, CT6 7YY, CT6 8QW

That's one row. All the postcodes are in one cell, separated by
commas. In order to perform the look-up of the postcodes (on another
spreadsheet), I need it to look like:

Barton Ward CT6 5RT
Barton Ward CT6 4JL
Barton Ward CT6 7YY
Barton Ward CT6 8QW

The first bit is easy - I used Excel's Text-to-Columns feature to
split the postcodes into separate columns, and then I remove the
leading spaces (using a macro I already have).

So now I have:

Barton Ward CT6 5RT CT6 4JL CT6 7YY CT6 8QW

.... and I need ...

Barton Ward CT6 5RT
Barton Ward CT6 4JL
Barton Ward CT6 7YY
Barton Ward CT6 8QW

I am aware that I could do this manually by inserting 3 more rows
underneath my one; then copying the last three postcodes, and doing
PasteSpecial (with Transpose) underneath CT6 5RT. Then copying
"Barton Ward" down three times.

However, with 2,000+ of these to do, I thought a VBA solution would
save a lot of time.

I'm just having trouble using the PasteSpecial feature in VBA,
really. The selection, inserting rows and copying work fine. But the
PasteSpecial gives an error. I was also wondering whether there might
be a more efficient, or easier, method than using PasteSpecial.

Can you suggest any method?

Steve