0

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!

3
Contributors
2
Replies
12
Views
2 Years
Discussion Span
Last Post by Slavi
0

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

0

Takes a range and copies it transposed

Worksheets("Sheet1").Range("A1:A5").Copy
Worksheets("Sheet2").Range("A1").PasteSpecial Transpose:=True
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.