Macro for relocating non adjacent columns

[MelanieGuinot]

Hello to All!

I have a data with more than 500 columns in excel xlsx file. the columns are like this:

A1 A2 A3... A15 B1 B2.... B15 C1 C2 C15... and so on. I like to relocate columns in such a way that it is A1B1C1D1...then A2B2C2...etc. Is there a macro that can do this relocation. Since there are more than 500 columns, copy paste is tedious. I am looking forward for your insight!

 

[MickG]

Perhaps:-

[COLOR="Navy"]Sub[/COLOR] MG18Feb42
ActiveSheet.Range("A1").CurrentRegion.Copy
Sheets("Sheet2").Range("A1").PasteSpecial Transpose:=True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[MelanieGuinot]

Hi Mick, Thanks for your code. However, i think i did not explain clearly . I have attached a structure of my data.

CURRENT:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A1[/TD]
[TD]A2[/TD]
[TD]A3[/TD]
[TD]B1[/TD]
[TD]B2[/TD]
[TD]B3[/TD]
[TD]C1[/TD]
[TD]C2[/TD]
[TD]C3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD]8[/TD]
[TD]4[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]0[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

Want to move columns and their data like this.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A1[/TD]
[TD]B1[/TD]
[TD]C1[/TD]
[TD]A2[/TD]
[TD]B2[/TD]
[TD]C2[/TD]
[TD]A3[/TD]
[TD]B3[/TD]
[TD]C3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]8[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]


There are 10000 rows totally. and columns spread from A1 to A35 and the B1 to B35 like that 560 columns

Thank you

 

[MickG]

Do these refer to header text for each column ??? :- A1 A2 A3 B1 B2 B3 C1 C2 C3

 

[MelanieGuinot]

The actual header is like this HVIDX$01 HVIDX$02... then HV101$01 HV101$02... The common identifier is last three char $01 or $02 etc. so the header with last three char $01 should all be adjacent followed by $02.

 

[MickG]

Please post an example of your actual data with an example of expected results !!!!