Moving text to another cell dynamically with macro

[Jersey22]

Hi everyone!
I´m new to the forum and VBA/macro-programming aswell.
I need help solving a quite easy(I think) problem.
The background
I've an excel with a couple of thousand information that should be on the following form
Account# data1 data2 data3
Right now, the excel looks like this:
Column A
A1 Account#
A2 data1 B2 data1 C2etc etc
A3 data2 B3 data2 C3etc etc
A4 data3 B4 data3
Blank row
A1 Account#
A2 data1 B2 data1 C2etc etc
A3 data2 B3 data2 C3etc etc
A4 data3 B4 data3
Blank row
and so on
What I need to do is to move all of the data1 to column B and all of the data2 to column C and so on..
I found a similar code but it doesn't carry all the information.
Sub CopyAndReformat()
Dim LastRow As Long, R As Long
Dim N As Long
Dim DstWks As Worksheet
Dim SrcWks As Worksheet
Set SrcWks = Worksheets("Sheet1") 'Name of the worksheet to copy from
Set DstWks = Worksheets("Sheet2") 'Name of the worksheet to coy to

'LastRow on the Source Worksheet
LastRow = SrcWks.Cells(Rows.Count, "A").End(xlUp).Row

For R = 1 To LastRow Step 3
N = N + 1
DstWks.Cells(N, "A") = SrcWks.Cells(R, "A")
DstWks.Cells(N, "B") = SrcWks.Cells(R + 1, "A")
DstWks.Cells(N, "C") = SrcWks.Cells(R + 2, "A")
Next R
End Sub

 

[pboltonchina]

Why not select column A and insert a column?

 

[Jersey22]

Ok, so I found a macro which is what I am looking for but need someone's help to expand on it. Thus far this macro only takes column A. I need the macro to copy of all information on the row of A2 and paste it on the end of row A1. Then go to row of A3 and copy the entire data and paste in on the end of row A1.

Sub TransSheet()
sSheet = ActiveSheet.Name
Sheets.Add
dSheet = ActiveSheet.Name
For x = 1 To 5000 Step 7
Sheets(sSheet).Activate
Range(Cells(x, 1), Cells(x, 1).Offset(4, 0)).Select
Selection.Copy
Sheets(dSheet).Activate
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
ActiveCell.Offset(1, 0).Select
Next x
End Sub<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>