VB loop row copy into a column

[MrTheMonium]

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!

[e.pol]

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

[Slavi]

Takes a range and copies it transposed

Worksheets("Sheet1").Range("A1:A5").Copy
Worksheets("Sheet2").Range("A1").PasteSpecial Transpose:=True