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

Dfats

New Member
Joined
Jan 18, 2017
Messages
4
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.
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

frank_AL

Active Member
Joined
Oct 30, 2015
Messages
439
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

New Member
Joined
Jan 18, 2017
Messages
4
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

New Member
Joined
Jan 18, 2017
Messages
4
Please ignore the formatting issues with my post. The email would be after the country.
 

Dfats

New Member
Joined
Jan 18, 2017
Messages
4
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

Active Member
Joined
Oct 30, 2015
Messages
439
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


Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,970
Messages
5,599,108
Members
414,288
Latest member
horizon2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top