Excel worksheet, macro, or formula to copy rows

[dkriese]

what is the best method to automate the copying of rows to another worksheet, macro, or formula.

I have a worksheet with rows of client data, I need to copy all the same client data to another worksheet i.e. with column headings

client id
name
location
hours

I may have client id 1004 listed in several rows on one sheet,I would like a formula to search the worksheet select all the rows with the same client id, copy those rows to another worksheet

 

[Zathras]

Not sure what you mean by automate. Do you mean that you have 100 clients and want to copy the data for each client to his own sheet? (Resulting in 101 sheets in the workbook.) If so, the easist way would be to use VBA.

On the other hand, to extract just client 1004 and copy those rows to a new sheet, the easiest way is to use Auto-Filter, select client 1004 and then copy and paste the rows from one sheet to the other.

Alternatively, you could sort the rows, but then you would need to copy the headings separately.

 

[dkriese]

The issue is I can't change the original worksheet as it was produced by the company, what they want is to have the person doing the data entry (who only knows data entry) be able to automate making separate worksheets for each client number for that month. The same client may appear on the one worksheet several times with different descriptions, and each of these rows has to go onto that client's worksheet. I will look into VBA. My concern is what is the best method to use for this, a macro a forumula or VBA

 

[KenWright]

I would think one option would be to use Data / Filter / Advanced Filter which allows you to specify the criteria for whuch rows youw ant copied, and will then copy them to another sheet without affecting your data. You could even set this up with VBA if you really wanted to to make it even more simple, but it really is an easy thing without VBA anyway.

Debra Dalgleish has some nice examples to give you a feel for what i'm describing:-

http://www.contextures.com/xladvfilter01.html

Even just using Autofilter and copying the visible rows left would work, and you can't get any simpler than that.

Regards
Ken...........