Macro to Copy Rows based on target cell value and paste in another sheet. Help Needed!

Hi All,

I have attached a sample workbook. I am trying to Run a Macro in the "Regional Stats" Sheet that will copy all rows from the "Data Summary" sheet where the cell in column J is less than -999 and paste them back in the "Regional Stats" sheet from row 6 on because there will be other information in rows 1-5.

The code I wrote clears the contents of all rows (6 down) in the regional stats sheet first so that duplicates don't occur and the info will always start at row 6 when the Macro is run twice. The Data Summary sheet will be updated periodically with new information but will also retain the old information so I don't want duplicate entries in the Regional Stats sheet every time I run the macro.

I can't seem to get it to work correctly so If someone could help me out it would be great! Also if anyone has input I would like to know if what I wrote is the most efficient way of achieving this. If there is a more efficient VBA script I would be all for it.

The Macro seems to work ok and then when the Data Summary sheet gets sorted it breaks. I need to be able to sort it as people will be adding data sorting the Data summary sheet and then Management will be using the Macro to pull only the info they need to the Regional Stats sheet to avoid looking through rows of data for what they want.

Here is the COPY AND PASTE code I am using

Please Login or Register  to view this content.

All input is greatly appreciated!!

Hi -

Try;

Please Login or Register  to view this content.

Regards,
event