Transpose Data From Column A to Multiple Rows

[c6m8]

Please forgive me if this has been previously posted and provide a link, but I was unable to find through my search.

I have a list in an Excel spreadsheet (Sheet 1) Column A of 4,800 contacts as follows:
Column A
1 Company 'a' Name
2 Company Street
3 Company City, State Zip
4 Company Phone
5 Company 'b' Name
6 Company Street
7 Company City, State Zip
8 Company Phone
9 Company 'c' Name
10 Company Street
11 Company City, State Zip
12 Company Phone
ETC...

I am trying to move the data to Sheet 2 as follows:

Column A Column B Column C Column D
1 Company 'a' Name Company Street Company City, State Zip Company Phone
2 Company 'b' Name Company Street Company City, State Zip Company Phone
3 Company 'c' Name Company Street Company City, State Zip Company Phone

Thank you in advance for your help!

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
With Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR - 3 Step 4
        Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 4).Value = Application.Transpose(.Range("A" & i).Resize(4))
    Next i
End With
End Sub

 

[c6m8]

Thank you for your timely reply!

I am going to sound r-e-a-l-l-y old and out-of-touch, but where do I enter this information in Excel 2007?

Thank you

 

[VoG]

Press ALT + F11 to open the Visual Basic Editor. On the Insert menu select Module then paste the code into the white space on the right.

Press ALT + Q to close the code window. Press ALT + F8 and double click test in the dialog that appears.

 

[c6m8]

It worked perfectly ! ! !

Now that I found this website, I will definitely be spending more time in these forums.

Again, thank you for your help and instruction.