MACRO to copy / paste cell text down various rowsSolved/Closed

Question

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!

ac3mark · Answer

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!

debtiger99 · Answer

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?

debtiger99 · Answer

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!

TrowaD · Answer

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

MACRO to copy / paste cell text down various rows

4 replies

Subscribe To Our Newsletter!