Hello VB Masters,

Firstly, apologize if my question is in the wrong place :)

I would like to create a loop that will copy a data from rows in the first sheet to the columns in the second sheet.

The data in the Sheet1 looks as follows:

A B C D E
F G H I J
K L M N O

And it needs to be:

A F K
B G L
C H M
D I N
E J O
A F K
B G L
C H M
D I N
E J O

(Etc.)

I am new to the topic so I am struggling with this loop.

So far I discovered that I can use some code as below. Of course, I have recorded this macro in Excel and checked the code.

Sheets("Sheet1").Select
    Range("A1:E1").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
Sheets("Sheet1").Select
    Range("A2:E2").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True

Row A B C D E from the Sheet1 contains headers of the table and the loop should end if it reaches blank cell at the end of Sheet1.

Could you please help me with this matter and offer some guidance?

Thank you!

I would like to guide some idea

  1. copy value look like this code

    DestSheet.Cells(desLine, i).Value = SrcSheet.Cells(srcLine, i).Value

  2. get the range of data

    Public Sub GetSheetRange(ws As Worksheet, NoRows As Long, NoCols As Integer)

    NoCols = ws.UsedRange.Columns.Count
    NoRows = ws.UsedRange.Rows.Count
    

    End Sub

Takes a range and copies it transposed

Worksheets("Sheet1").Range("A1:A5").Copy
Worksheets("Sheet2").Range("A1").PasteSpecial Transpose:=True
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.