Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

MACRO to copy / paste cell text down various rows [Solved/Closed]

Report
Posts
4
Registration date
Wednesday August 1, 2018
Status
Member
Last seen
August 10, 2018
 -
Posts
2691
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 14, 2021
 -
Hello,
I have an enormous list sent to me of subcontractors who work for different companies and each employee is listed alphabetically within their own company. Each employee's details are in several columns across 1 row.
Column A is the company name but is listed ONLY once for the first employee. The remaining employees have a blank cell in A. The next row with text in Column A is the first line of the next company and the pattern repeats.

My problem is that I need to create ONE alphabetical list of all people with every person's company listed in the A column.

I was hoping to create a macro that would copy A2 down however many rows that are blank until it reaches the next cell with text, then proceed to copy the next company text down the column for however many employees it has etc.

Bob's Bikes Tom
John
Bill
Sam
Sara's Kayaks Mary
Sheri
Bob's Drones Todd
Kurt
Sheila
Laura
Kim
Margaret
Joe
Kevin's Canoes Marti
Janet
Deborah
Steve

Even though this is not a spreadsheet - hopefully it will give you an idea of what I'm referring to.

Any help is GREATLY appreciated!
See more

4 replies

Answer 1 / 4

Click on the Name column, and SORT by ALPHA! No need for a macro. If it says that there is other data not selected, say YES select that data too! If you say no, the name only will be moved, and the the rest of the columns will not be sorted with the original record!
SIMPLE!


Answer 2 / 4
Posts
4
Registration date
Wednesday August 1, 2018
Status
Member
Last seen
August 10, 2018
Thanks for the response but unfortunately it does not produce the result I need. Also, the format of my original note did not stay the same when I hit enter. Imagine this: Column A has the company names, Column B the employee's last names. Only the first employee in the list of 15 people indicates the name of the company and the remaining employees have a blank cell for their company name because it is easy to see who they work for since they're all together. My problem is that my list has over 50 companies and I would like to have an alphabetized list of all employees based on last name. If I sort the list as is - I have tons of people with no company name and I don't know who they work for. I need to be able to copy down the company names throughout my list so when I sort it by last name each individual has their company listed. Does this make more sense?
Answer 3 / 4
Posts
4
Registration date
Wednesday August 1, 2018
Status
Member
Last seen
August 10, 2018
The process that I am using currently is this:

Double click the little black cross in the bottom right corner and the company name copies down to the next company name. So I am repeatedly doing this:

[END] [Down Arrow] [Double click copy cross]
[END] [Down Arrow] [Double click copy cross]
[END] [Down Arrow] [Double click copy cross]
[END] [Down Arrow] [Double click copy cross]

over and over and over....

I tried to create a macro but however the macro mimics the EXACT number of rows that were copied down instead of stopping when it reaches the next cell with text.

Thank you for taking the time to assist me!
Answer 4 / 4
Posts
2691
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 14, 2021
 454
Hi Debtiger,

Consider using this macro to do the task for you: 
Sub RunMe()
Dim x, lRow As Long

lRow = Range("B1").End(xlDown).Row

For x = 2 To lRow
    If Cells(x, 1) <> vbNullString Then
        Cells(x, 1).Copy
    Else
        Cells(x, 1).PasteSpecial
    End If
Next x

Application.CutCopyMode = False
End Sub


Best regards,
Trowa
Report
Posts
4
Registration date
Wednesday August 1, 2018
Status
Member
Last seen
August 10, 2018
TrowaD ~

I can't BEGIN to express how GRATEFUL I am to YOU!!!!!!!!!!!!!!!!!!!!!!!!!! Your VBA Code worked absolutely perfectly. I am blown away! I'm good with excel but not with code - and with the help of an online tutorial I was able to put your code to work. I have never posted an issue before and wasn't quite sure what to expect, but for a complete stranger to take the time to read all the details of my problem and spend their own time to work on a solution.... is a true indicator of the generous human spirit.

Thank you ever so much for your time and assistance.
I hope you have the most splendid day.
(insert the HAPPIEST emoticon here!)
Debra
Report
Posts
2691
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 14, 2021
 454
Thanks Debra. Your comment makes it all worthwhile!
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!