VBA Question - How to code copy/paste of selection of cells (including first and last)

[Dfats]

I am very new to VBA and I am trying to learn it in my personal time. Can someone help me with creating a macro a macro that will select a range of cells and cut/paste them into another area?

Background info:
I was given a customer list that is not organized into any columns. All of the customer info is entered in column A. All customers have the first line being their name (which I have highlighted blue) and the last line is the email (highlighted green). All of the information in between is address info.

My manager has asked me to put this data into columns so that it can be used to create a database.

Objective:
Highlight first row (name with interior color of 13995347) and all of the cells in between including the last row (email with interior color of 5296274) which will then paste into cells D2:J2 etc

Have it loop to go through all 816 customers and paste into the next blank cells ie D3:J3, D4:J4 etc.

If anyone could help me with this that would be great. I have no idea what I am doing lol. Thank you very much for your time.

 

[frank_AL]

Can you post something that would be representative of one person's information? Include the Field Name you want to use for each item.

 

[Dfats]

Mooney, Joe <-- Highlighted blue

Lifetime Member

Cost Service Center

P.O. Box 9-220 Judung

Hsinchu County 31334

Taiwan

<colgroup><col></colgroup><tbody>
</tbody>

lcmsosolar@gmail.com <--- highlighted green All customer names and emails have been highlighted their respective colors to indicate the start/end

<tbody>
</tbody>

 

[Dfats]

Please ignore the formatting issues with my post. The email would be after the country.

 

[Dfats]

Ooops forgot to include the different columns. The field names that would be used are:


Name
Member Status
Institute Name
Address 1
Address 2
City
Country
Email

Thanks for your help!

 

[frank_AL]

Try this code, you can change the shade of Blue and Green by modifying the settings for Red, Green, Blue in the lines of code that start with:
ActiveCell.Interior.Color = RGB

Sub RearrangeData()


Range("D1").Select
NextRow = Cells(Rows.Count, "D").End(xlUp).Row
NextRow = NextRow + 1
EndRow = 1
Range("A1").Select
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Do While EndRow < LastRow
    StartRow = ActiveCell.Row
    EndRow = StartRow + 6
    Range("A" & StartRow & ":A" & EndRow).Select
    Selection.Copy
    Range("D" & NextRow).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Application.CutCopyMode = False
    NextRow = NextRow + 1
    Range("A" & StartRow).Select
    ActiveCell.Interior.Color = RGB(51, 204, 204)
    Range("A" & EndRow).Select
    ActiveCell.Interior.Color = RGB(153, 204, 0)
    ActiveCell.Offset(1, 0).Select
Loop
End Sub