Transfer from report

[rjplante]

I have a department listing page which has the department names in Columns ACE...etc. This is the sheet I want to transfer into. Call it Sheet A. I also have a spreadsheet that is generated from a report from some accounting software. Call it Report B. Report B has two columns, Column A has employee names, and column B has department names and business office codes (formatted as 100-Sales, 200-Marketing, etc.) I have written part of the macro which separates out the codes from the names so now I have three columns, Col A for names, B for codes, and C for department names. The part I am trying to write is to cope all of the names for a particular department from Report B and then paste them into the corresponding section on Sheet A. How do I grab the correct number of cells from Report B for a particular department? I have a couple of ideas, but not sure of the most efficient.

1) Do a count of all the items in Col C that says "Sales" and store that as DeptCount number.
2) Run a loop to find the first item in the column that matches the "Sales" name and then offset(0,-2) to get to the first employees name from that department
3) Then use Range(Activecell: Activecell.Offset(DeptCount,0).Copy to capture the full range
4) Go to Sheet A, select the desired start cell Range("A2").paste to paste the values into.

Or is there a better way like defining the range as Dept_Sales and then setting the range equal to the total rows for that department.
Dept_Sales = Range(Activecell: Activecell.Offset(DeptCount,0)

The simply use range("A2").value = Dept_Sales to paste them in. I don't know if this method requires the sames number of cells in the range to be selected in Sheet A before making it equal to the defined range from Report B.

Please help direct me to the most efficient way to transfer these names over.

Thanks in advance for all the help.

Robert