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

debtiger99 4 Posts Wednesday August 1, 2018Registration date August 10, 2018 Last seen - Aug 1, 2018 at 03:54 PM - Latest reply: TrowaD 2438 Posts Sunday September 12, 2010Registration dateModeratorStatus October 16, 2018 Last seen
- Aug 14, 2018 at 11:26 AM
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 

Your reply

6 replies

ac3mark 10668 Posts Monday June 3, 2013Registration dateModeratorStatus October 17, 2018 Last seen - Aug 1, 2018 at 03:59 PM
0
Thank you
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!


Respond to ac3mark
debtiger99 4 Posts Wednesday August 1, 2018Registration date August 10, 2018 Last seen - Aug 2, 2018 at 07:27 AM
0
Thank you
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?
Respond to debtiger99
debtiger99 4 Posts Wednesday August 1, 2018Registration date August 10, 2018 Last seen - Aug 2, 2018 at 07:36 AM
0
Thank you
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!
Respond to debtiger99
TrowaD 2438 Posts Sunday September 12, 2010Registration dateModeratorStatus October 16, 2018 Last seen - Updated by TrowaD on 2/08/18 at 11:16 AM
0
Thank you
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
debtiger99 4 Posts Wednesday August 1, 2018Registration date August 10, 2018 Last seen - Aug 10, 2018 at 02:12 PM
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
TrowaD 2438 Posts Sunday September 12, 2010Registration dateModeratorStatus October 16, 2018 Last seen - Aug 14, 2018 at 11:26 AM
Thanks Debra. Your comment makes it all worthwhile!
Respond to TrowaD