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
– Luuklag, Commented Apr 17, 2018 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 — Pᴇʜ
– Pᴇʜ, Commented Apr 17, 2018 at 14:43

warner_sc · Accepted Answer · 2018-04-17 14:53:06Z

1

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

answered Apr 17, 2018 at 14:53

warner_sc

8486 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

score 1 · Accepted Answer · 2018-04-17 15:26:54Z

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, 2018 at 15:26

answered Apr 17, 2018 at 14:55

user8608712

3 Comments

Pᴇʜ Over a year ago

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ᴇʜ Over a year ago

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.

user8608712 Over a year ago

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...

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related