MACRO to copy / paste cell text down various rows [Solved]
debtiger994
Posts Wednesday August 1, 2018Registration date
August 10, 2018
Last seen
-
Aug 1, 2018 at 03:54 PM - Latest reply: TrowaD2411
Posts Sunday September 12, 2010Registration dateModeratorStatus
August 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.
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!
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?
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:
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.
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
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
