Copy paste rows based on criteria

**jeff p** · 12-07-2014, 06:32 PM

I came across this code which copies the rows with a unique cell value from sheet1 and pastes these in sheet2.

How can this be modified so that it pastes the new rows in sheet2 within a range.
Range ("I20")
..another words, to offset the new rows from the cell I20 ?

Any help to fix would be greatly appreciated!

Please Login or Register  to view this content.

**Richard Buttrey** · 12-07-2014, 07:47 PM

Hi,

Maybe with

Please Login or Register  to view this content.

**jeff p** · 12-07-2014, 09:16 PM

Thanks for the reply, Ricihard.

I've tried to add your code .. and variations of this with no luck.

Please Login or Register  to view this content.

..Also... , is there a way to change the paste functionality to insert. ?
ie
Selection.Insert Shift:=xlDown

Thanks for all the help on this!

**Richard Buttrey** · 12-08-2014, 05:00 AM

Hi,

You seem to have added additional stuff to the code I gave you and it's by no means clear what you mean by paste 'within a range I20"

You'd be better advised to upload the workbook and manually add the results you expect to see. I believe your code is far too complex for the simple task I think you're trying to achieve.

Whenever you find yourself using a loop (For..Next, Do..Loop etc) to copy/delete or otherwise do stuff with specific cells / rows ask yourself if there isn't an easier way. In most cases there is and a simple data filter (either an AutoFilter or Advanced Filter) coupled with a .SpecialCells(xlCellTypeVisible).Copy instruction will do the job far more efficiently and with no more than a couple or so lines of code.

Filtering is the fastest way of doing this stuff and avoids inefficient and slow looping procedures. This really should be a FAQ.

**jeff p** · 12-08-2014, 11:00 PM

Thanks Richard.

I agree using the auto filter is very simple and efficient .. It's attempting to copy an entire row and paste/ offset from a specified range that gave me trouble...

I've attached a simple file for you to possibly look at.

Many thanks!