Help Required to Transpose Every X Number of column in Excel with a Macro



I want to transpose X number of column via macro to rows exactly the opposite of what has been done in this video : 

Sheet1 : Is how many date is Placed

Sheet2 : Is How i want it

(File Attached)

I am a teacher and this is an important step to allot courses to my students.

Please anyone could help me  to run macros for this.




Hello, Basilio

This worked really well,Thanks a lot 

As this is an ongoin process to add students with multiple details,so in the next few days i may have to add their email address,contact info etc the same way as i have added courses
Can you please guide if i add a few coloumn in between them and then want to run this micro what changes in this code would be required

Once more i would like to thank you,this saved lot of time or else i had planned to do this manually .


Avin (rep: 2) Jun 3, '20 at 2:11 am
Hi, Avin

If you just add columns at the end or in between you do not have to move anything, the macro will keep working unless you leave an empty column.

Anyway, if something does not work I am happy to help.

Regards Basilio
Basilio (rep: 105) Jun 4, '20 at 11:10 am
Add to Discussion


Selected Answer

Hello, Avin

I attached a file with the following macro:

Sub Transpose()

i = 2
k = 2

Do While Cells(i, 1) <> ""

    firstname = Cells(i, 1)
    lastname = Cells(i, 2)
    j = 3

    Do While Cells(1, j) <> ""

        Sheets("Sheet2").Cells(k, 1) = firstname
        Sheets("Sheet2").Cells(k, 2) = lastname
        Sheets("Sheet2").Cells(k, 3) = Cells(i, j)
        j = j + 1
        k = k + 1

    i = i + 1


End Sub

Please, try it. It worked for me. Just take in acount if you change the name of the sheet2 you must change it to in the macro. Tha macro must be run from sheet1.

I hope this help you. Any comment or doubt, please let me know.



Answer the Question

You must create an account to use the forum. Create an Account or Login