Any one can do Macro using loop that can transpose multiple rows into columns

Question

Hy i have a recorded macro, i am trying to use it using loop so that i can transpose multiple rows and columns in one click. The idea is that i have a number 10000 rows with columns 1000 in which emails are there.i want to use macro that transpose my rows data into columns using do while or loop. I have record the macro but it only work for one one row and column. The code is there.

Sheets("Mastersheet").Select
Range("J2:XFD2").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True

Go look for a tutorial on how to write a loop. We are happy to help you with any problems, but please do the basics yourself. — Luuklag, Apr 17 '18 at 14:41
You don't need a loop. Just select ALL rows, then copy and paste transposed. It works with more than one row at once. See: [Transpose (rotate) data from rows to columns or vice versa](https://support.office.com/en-us/article/transpose-rotate-data-from-rows-to-columns-or-vice-versa-3419f2e3-beab-4318-aae5-d0f862209744) — Pᴇʜ, Apr 17 '18 at 14:43

score 1 · Accepted Answer · answered Apr 17 '18 at 14:53

You can resolve your problem faster if you at least try and search for answers and information.

Excel VBA - Range.Copy transpose paste

Code:

Option Explicit

Sub test()

Dim master_sheet As Worksheet
Set master_sheet = ThisWorkbook.Sheets("Mastersheet")

Dim output_sheet As Worksheet
Set output_sheet = ThisWorkbook.Sheets.Add

Dim start_row As Long
start_row = 2
Dim last_row As Long

With master_sheet
last_row = .Cells(.Rows.Count, "J").End(xlUp).Row
.Range("J" & start_row & ":XFD" & last_row).Copy
End With

With output_sheet
.Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End With

End Sub

score 1 · Answer 2 · 2018-04-17T15:26:54.230

1

Try this:

Sub Macro9()

t = 2

Do Until t = 10000

    Sheets("Mastersheet").Range("J" & t & ":XFD" & t).Copy
    Sheets("Sheet2").Select
    Sheets("Sheet2").Cells(1, t).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
t = t + 1        
Loop
End Sub

edited Apr 17 '18 at 15:26

answered Apr 17 '18 at 14:55

This won't work because it will run forever → Endless loop. Also if you need a counter (eg. `t`) it is easier to use a `For` loop instead a `Do` Loop. – Pᴇʜ Apr 17 '18 at 15:07
That's why I suggest a `For t = 2 To 10000` loop. Because you cannot forget it here. The `Next t` in the end of the loop automatically increments `t`. Counter loops are much easier with `For`. – Pᴇʜ Apr 17 '18 at 15:28
Now it work perfectly (I tested it). But you can use For to next etc too :) I used loop because the poster was asking for one... – Apr 17 '18 at 15:31

Any one can do Macro using loop that can transpose multiple rows into columns

2 Answers2