Within a loop, need to copy all rows from one sheet to another upon matching criteria

Using Excel 2003 in XP...I have a database worksheet ("DATA") that I want to copy certain rows of data to multiple worksheets within the same workbook ("KING", "PIERCE", etc.) if it matches the County (e.g. King, Pierce, etc.) of the same worksheet name. Worksheet DATA has 3 header rows and data is in columns A:J but the rows will vary from day to day. Wanting to do the following: for the first row of data in DATA that contains the County (column B in DATA) of the corresponding spreadsheet (e.g. KING), I want to copy everything in that row from DATA, paste in the corresponding spreadsheet, and then continue to the next row in DATA.

I had planned on doing it long-hand as below and write a separate section for each County Spreadsheet (4 of them), but the code is not selecting just a single row in DATA but the entire sheet prior to copying and pasting. At a minimum, I think I just need this line of code below - Range("A" & Row & ":J" & Column).Select - corrected because it is selecting the entire worksheet from A4:J258 - not just all of row 4 (or A4:J4) which is what I need - and then I can just copy and paste the code for the remaining 3 counties. There might also be something wrong in Set OC = ws1.Cells(King.Row, Group.Column) too?

However, if there is an easier/faster code to just go through the DATA sheet once (instead of 4 times) and have it copy a row to the corresponding County sheet and paste/append to that sheet, that would be greatly appreciated.

First time user so please pardon any forum violations and let me know what I need to change.

Thanks!

Please Login or Register  to view this content.

Please see attached test file. Thanks!