Copy values in row to columns in spaces

Solved/Closed
Report
-
 LearnVBA -
Hello,

I am a beginner in VBA, and would mcuh appreciate some guidance on transposing data in a row to a column via VBA.

My row data is as follows:

Column A
Datapoint1
Datapoint2
Datapoint3
Datapoint4

I can simply copy and paste (transpose), but the result will be like this.
Column A, Column B Column C
Datapoint1 Datapoint2 Datapoint3

What I am trying to achieve is
Column A, Column B, Column C, Column D, Column E, Column F, Column G
Datapoint1, empty, empty, empty, empty, empty, Datapoint 2

Datapoint 3 would appear in Column M. There are 5 columns in between.

There are about 3,000 Datapoints that need to be transposed into column (separated by 5 columns in between).

Your help would be much appreciated.

Thank you,




3 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
try this macro and see sheet2

Sub test()
Dim r As Range, c As Range
Dim r2 As Range
Worksheets("sheet2").Cells.Clear
Worksheets("sheet1").Activate
Set r = Range(Range("A1"), Range("A1").End(xlDown))
With Worksheets("sheet2")
Set r2 = .Range("A1")
End With

For Each c In r
c.Copy r2.Offset(0, 6)
With Worksheets
Set r2 = r2.Offset(0, 6)
End With
Next c
With Worksheets("sheet2")
Range(.Range("A1"), .Range("a1").Offset(0, 5)).EntireColumn.Delete
End With
End Sub
Thank you very much, this works miraculously.
I have first copy and pasted the formulae, and it works. I am reading line by line to interpret them and revise to the worksheet format I am working on.

Thanks very much, I envy this skill set you have...
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
you are welcome
I just came back to say thank you again. I am using this code again today.
I have been reading and understanding line by line. This will be a very helpful tool for me in many occasion. I will try to also support this site by participating (once I have build some knowledge).

thank you,