VB Novice...Find and Move Cells...

[jh1201]

Hi Forum.

Have a "task" that's been handed to me to conduct some analysis for market stats. The problem is the data I have to work with has "headers" actually contained within each row versus being formatted nicely in columns.

Example:
Each row of my worksheet contains data for an individual client. Within each client (row) there are employee counts per company assigned to that client. The cell structure in each row is:
Client Name Address, etc./the literal "EMPLOYEES"/Company#/data/data/"EMPLOYEES"/Company#/data/data/EMPLOYEES/ and continues on in that pattern in each row reporting each company in the same row.

Problem: The cells are not aligned. A client could have one company or 100 companies....all going across the same row.

I understand the logic just don't know how to write it. I need the macro to ultimately "find" the text "EMPLOYEES" and copy the next 3 cells to the right (company#/data/data) to a new worksheet so I can have all of the companies' data in one sheet and in two columns so I can analyze the numbers (highs, lows, averages, etc.). I don't necessarily need the company# copied, but figured it would be easier just to grab all 3 cells to the right of "EMPLOYEES" whereever the literal "EMPLOYEES" is found.

Hope this makes sense.

I am using Excel2007


thanks for any help!
JohnH

[antoka05]

Please may you enclose a little example of your workbook so we can help you better.

Regards,
Antonio

[jh1201]

I'm attaching a small sample of the over 6000 records (rows). Columns A through G are specific to the client's name and address so I've replaced those with 'xx'. The relevant data starts with column H.

Again the logic I'm looking to use would be to programatically find the literal "EMPLOYEES" and copy and paste the next 3 cells to the right of "EMPLOYEES" into a new worksheet - each copy/paste of data creating a new row in the new worksheet.

Hope that helps! Thanks Antonio.

John

[StephenR]

Like so perhaps?

Please Login or Register  to view this content.

[jh1201]

StephenR. Looked too simple, but worked like a charm. Thanks.

[antoka05]

If you need also companies code you can add blue row:

Please Login or Register  to view this content.

Regards,
Antonio