Use Macros to copy and paste transpose through a range of data

Need help pls.

Pls see attached. I got data in column A. (yellow in colour)
Need to copy each running 8 rows and paste them onto Column E to L.

As I got thousands of records in column A, its very tedious to do copy and transpose paste one by one.
Can advise how to use macro on this?

Hi zxchan,
try it

Please Login or Register  to view this content.

Try the attached.

Please Login or Register  to view this content.

Ankit,
Are you sure the code you posted is meant to be for this thread?
The OP has asked to transpose a single sheet, in a single column in to rows. Why do you need to loop through sheets?

Many thanks! It helps.

But raninto another problem. Realised that not all records are in sets of 8.

1st field is doctor's name, 2nd field is clinic address, 3rd to 5th field is address, 6th field is a hyperlink, 7th field is doctor's specialty, 8th field is telephone number,

The problem is that sometimes the addres is quite short and only takes up field 3 and 4 and field, and 5th field becomes hyperlink, 6th field becomes speciality, 7th field becomes telephone number.

Any way for the macro to recognise that hyperlink will only take up the 6th field and 5th field is to be left blank if the address only takes up 3rd and 4th field.


Pls see attached for the data. Thanks again for the help.

maybe so

Please Login or Register  to view this content.

Note: there are no hyperlinks in cells A13 and A21 in your example

Thanks Nilem!

It works well, but I realised that the 1st field - doctor's name is also a hyperlink.. and the macro runs into problem.

Need your help again pls. Instead of getting the macro to recognise the hyperlink, how to get the macro to recognise a string of words "View more info".
The words "View more info" will only take up the 6th field and 5th field is to be left blank if the address only takes up 3rd and 4th field

Thanks again

try

Please Login or Register  to view this content.

Thanks Nilem, you are awesome.

One last thing pls.

Possible for the "view more details" be a hyperlink as well? Currently its transposed into a text
Currently, if the address field is short, it will take up field no.3 and 4. Is it possible for short address to take up field no. 3 and field no.5 and field no.4 will be left blank?

Thanks, appreciate very much