Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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

0

Hello

I want to transpose X number of column via macro to rows exactly the opposite of what has been done in this video : https://youtu.be/BOQKWt8zAKs 

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.

Thanks

excel
MS Office Version: Office 365
posted by: Avin (rep: 2) on Tue Jun 2, 2020 at 5:41 pm
Answer
Discuss

Discussion

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 .

Regards
Avin
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

Answers

0
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
    Loop

    i = i + 1

Loop

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.

Regards
Basilio

posted by: Basilio (rep: 105) on Tue Jun 2, 2020 at 11:19 pm
Discuss


Answer the Question

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