Convert Multiple Columns to Rows depending on an Input Value

[prkhan56]

Hello All,
I have lot of data in Excel 2010 which I wish to bring in Columns using a Macro depending on the input value which the macro should prompt me.
For Eg:

A1 B1 C1 D1 E1 F1 G1 H1 I1 J1
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10
1 2 3 4 5 6 7 8 9 10


If I select data from A1 and J1 (in practical it will be more Columns) the Macro should prompt me how many Columns would be the output on Master Sheet.
If the input is 2 then it should create an output Sheet ‘Master’ and should show the following result
A1 B1
1 2
1 2
1 2
1 2
1 2
3 4
3 4
3 4
3 4
3 4
5 6
5 6
5 6
5 6
5 6
.. ..
.. ..
It after selection I run the macro and input 3 then the output should go in three columns (A1,B1,C1) one below other. If 4 is Input then 4 Columns (A1,B1,C1,D1) will come below each other so on and so forth.
I hope this gives the idea.
Any help would be greatly appreciated.
TIA
Rashid Khan

[PCI]

Try next code

Please Login or Register  to view this content.

[jindon]

Try

Please Login or Register  to view this content.

[prkhan56]

Hello Jindon,
Thanks for the prompt reply. Your code works great. I need to get some modifications please.

Sorry I missed the Heading Part in my original post. First Row within the Selection would be Headings. It copies the Headings too.
See Master Sheet in the Sample file attached.
I have mentioned the results required.

Also would it be possible to modify the code to work on all the sheets in the Workbook except the Master sheet and append the data of each sheet below in the Master Sheet.

Thanks once again.

You are a great help.
Rashid Khan

[prkhan56]

Hello PCI,
Thanks for the code.
But it does not give the desired result as per my original post.
Code provided by Jindon suits my requirement.
Thanks for your time.
Rashid Khan

[jindon]

Not really sure if this is what you wanted.

Please Login or Register  to view this content.

[prkhan56]

Works like a charm!
Thanks a lot for your time and help
Really appreciated
Rashid Khan